Как превратить массив json в массив postgres?

У меня есть столбец data , который содержит документ json примерно так:

  {
    «name»: «foo»,
    "tags": ["foo", "bar"]
}
 

Я хотел бы превратить массив вложенных тегов в конкатенированную строку ( foo, bar ). Это было бы легко возможно с помощью функции array_to_string () в теории. Однако эта функция не действует на массивы json . Поэтому мне интересно, как превратить этот массив json в массив Postgres

?

50 голосов | спросил Christoph 3 TueEurope/Moscow2013-12-03T00:48:12+04:00Europe/Moscow12bEurope/MoscowTue, 03 Dec 2013 00:48:12 +0400 2013, 00:48:12

5 ответов


67

Postgres 9.4 или новее

Очевидно, что , вдохновленный этим сообщением , Postgres 9.4 добавил отсутствующие функции:
Спасибо Лоуренсу Роу за патч и Андрею Данстану за совершение!

Чтобы отключить массив JSON. Затем используйте array_agg () или ARRAY для создания массива Postgres . Или string_agg () , чтобы создать текст text string .

Совокупность неучтенных элементов в строке в LATERAL или коррелированном подзапросе. Затем исходный порядок сохраняется , и нам не нужен ORDER BY , GROUP BY или даже уникальный ключ во внешнем запросе. См:

Замените 'json' на 'jsonb' для jsonb во всем следующем коде SQL.

  SELECT t.tbl_id, d.list
FROM tbl t
CROSS JOIN LATERAL (
   SELECT string_agg (d.elem :: текст, ',') Список AS
   FROM json_array_elements_text (t.data-> 'tags') AS d (elem)
   ) d;
 

Короткий синтаксис:

  SELECT t.tbl_id, d.list
FROM tbl t, LATERAL (
   SELECT string_agg (значение :: текст, ',') Список AS
   FROM json_array_elements_text (t.data-> 'tags') - имя столбца по умолчанию: "value"
   ) d;
 

по теме:  - В чем разница между LATERAL и подзапрос в PostgreSQL?

Конструктор ARRAY в коррелированном подзапросе:

  SELECT tbl_id, ARRAY (SELECT json_array_elements_text (t.data-> 'tags')) AS txt_arr
FROM tbl t;
 

по теме:

Тонкие различия : null элементы сохраняются в реальных массивах . Это невозможно в приведенных выше запросах, создавая строку text , которая не может содержать значения null . истинное представление представляет собой массив.

Обертка функций

Для повторного использования, чтобы сделать это еще проще, инкапсулируйте логику в функцию:

  СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ json_arr2text_arr (_js json)
  RETURNS text [] ЯЗЫК sql IMMUTABLE AS
'SELECT ARRAY (SELECT json_array_elements_text (_js))';
 

Сделать это SQL-функцией , поэтому это может быть inlined в более крупных запросах.
Сделайте его IMMUTABLE (потому что он), чтобы избежать повторной оценки в больших запросах и разрешить его в индексных выражениях.

Вызов:

  SELECT tbl_id, json_arr2text_arr (data-> 'tags')
FROM tbl;
 

db> скрипка здесь


Postgres 9.3 и старше

Используйте функцию json_array_elements () . Но мы получаем двойные кавычки .

Альтернативный запрос с агрегацией во внешнем запросе. CROSS JOIN удаляет строки с отсутствующими или пустыми массивами. Может также быть полезным для обработки элементов. Нам нужен уникальный ключ для агрегации:

  SELECT t.tbl_id,string_agg (d.elem :: text, ',') Список AS
FROM tbl t
CROSS JOIN LATERAL json_array_elements (t.data-> 'tags') AS d (elem)
GROUP BY t.tbl_id;
 

Конструктор ARRAY, все еще содержащий строки:

  SELECT tbl_id, ARRAY (SELECT json_array_elements (t.data-> 'теги)) AS quoted_txt_arr
FROM tbl t;
 

Обратите внимание, что null преобразуется в текстовое значение «null», в отличие от выше. Неправильно, строго говоря, и потенциально неоднозначно.

Безнадежный человек с помощью trim () :

  SELECT t.tbl_id, string_agg (trim (d.elem :: text, '"'), ',') AS list
FROM tbl t, json_array_elements (t.data-> 'tags') d (elem)
ГРУППА ПО 1;
 

Получить одну строку из tbl:

  SELECT string_agg (trim (d.elem :: текст, '' '),', ') Список AS
FROM tbl t, json_array_elements (t.data-> 'tags') d (elem)
WHERE t.tbl_id = 1;
 

Строки образуют коррелированный подзапрос:

  SELECT tbl_id, (SELECT string_agg (обрезка (значение :: текст, '"'), ',')
                FROM json_array_elements (t.data-> 'tags')) Список AS
FROM tbl t;
 

Конструктор ARRAY:

  SELECT tbl_id, ARRAY (SELECT trim (значение :: текст, '' ')
                     FROM json_array_elements (t.data-> 'tags')) AS txt_arr
FROM tbl t;
 

Оригинал (устаревший) SQL Fiddle . < br> db> скрипка здесь.

по теме:

Примечания (устаревшие с п. 9.4)

Нам понадобится json_array_elements_text (json) , двойник json_array_elements (json) , чтобы вернуть text из массива JSON. Но это кажется отсутствующим из предоставленного арсенала функций JSON . Или какую-либо другую функцию для извлечения значения text из значения скалярного JSON . Кажется, мне тоже не хватает этого. Поэтому я импровизировал с помощью trim () , но выйдет из строя для нетривиальных случаев ...

ответил Erwin Brandstetter 3 TueEurope/Moscow2013-12-03T01:56:51+04:00Europe/Moscow12bEurope/MoscowTue, 03 Dec 2013 01:56:51 +0400 2013, 01:56:51
14

PG 9.4 +

Принятый ответ определенно то, что вам нужно, но для простоты здесь используется помощник:

  СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ jsonb_array_to_text_array (
  p_input jsonb
) RETURNS TEXT [] AS $ BODY $

DECLARE v_output text [];

НАЧАТЬ

  SELECT array_agg (ary) :: text []
  INTO v_output
  FROM jsonb_array_elements_text (p_input) AS ary;

  RETURN v_output;

КОНЕЦ;

$ BODY $
LANGUAGE plpgsql VOLATILE;
 

Затем просто выполните:

  SELECT jsonb_array_to_text_array ('["a", "b", "c"]' :: jsonb);
 
ответил andrew.carpenter 6 +03002015-10-06T02:56:48+03:00312015bEurope/MoscowTue, 06 Oct 2015 02:56:48 +0300 2015, 02:56:48
7

Этот вопрос задавали на списки рассылки PostgreSQL , и я придумал этот хакерский способ преобразования текста JSON в текстовый тип PostgreSQL с помощью оператора извлечения поля JSON:

  CREATE FUNCTION json_text (json) RETURNS text IMMUTABLE LANGUAGE sql
AS $$ SELECT ('[' || $ 1 || ']') :: json->> 0 $$;

db = # select json_text (json_array_elements ('["hello", 1.3, "\ u2603"]'));
 json_text
-----------
 Здравствуйте
 1,3
 â~ƒ
 

В основном он преобразует значение в одноэлементный массив и затем запрашивает первый элемент.

Другим подходом было бы использовать этот оператор для однократного извлечения всех полей. Но для больших массивов это, скорее всего, медленнее, поскольку для каждого элемента массива необходимо проанализировать всю строку JSON, что приводит к сложности O (n ^ 2).

  CREATE FUNCTION json_array_elements_text (json) RETURNS SETOF текст IMMUTABLE LANGUAGE sql
AS $$ SELECT $ 1->> i FROM generate_series (0, json_array_length ($ 1) -1) AS i $$;

db = # select json_array_elements_text ('["hello", 1.3, "\ u2603"]');
 json_array_elements_text
--------------------------
 Здравствуйте
 1,3
 â~ƒ
 
ответил intgr 20 Jpm1000000pmMon, 20 Jan 2014 14:50:37 +040014 2014, 14:50:37
0

Я проверил несколько вариантов. Вот мой любимый запрос. Предположим, что у нас есть таблица, содержащая поле id и json. Поле json содержит массив, который мы хотим превратить в массив pg.

  SELECT *
ОТ теста
WHERE TRANSLATE (jsonb :: jsonb :: text, '[]', '{}') :: INT []
       & Amp; & Amp; ARRAY [1,2,3];
 

Он работает где угодно и быстрее, чем другие, но выглядит суровым)

Сначала массив json вытесняется как текст, а затем мы просто меняем квадратные скобки в скобки. Наконец, текст создается как массив требуемого типа.

  SELECT TRANSLATE ('[1]' :: jsonb :: text, '[]', '{}') :: INT [];
 

, и если вы предпочитаете text [] массивы

  SELECT TRANSLATE ('[1]' :: jsonb :: text, '[]', '{}') :: TEXT [];
 
ответил FiscalCliff 5 MonEurope/Moscow2016-12-05T11:33:47+03:00Europe/Moscow12bEurope/MoscowMon, 05 Dec 2016 11:33:47 +0300 2016, 11:33:47
0

Эти несколько функций, взятые из ответов на этот вопрос , являются тем, что я использую, и они отлично работают

  СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ json_array_casttext (json) RETURNS text [] AS $ f $
    SELECT array_agg (x) || ARRAY [] :: text [] FROM json_array_elements_text ($ 1) t (x);
$ f $ ЯЗЫК sql IMMUTABLE;

СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ jsonb_array_casttext (jsonb) RETURNS text [] AS $ f $
    SELECT array_agg (x) || ARRAY [] :: text [] FROM jsonb_array_elements_text ($ 1) t (x);
$ f $ ЯЗЫК sql IMMUTABLE;

СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ json_array_castint (json) RETURNS int [] AS $ f $
    SELECT array_agg (x) :: int [] || ARRAY [] :: int [] FROM json_array_elements_text ($ 1) t (x);
$ f $ ЯЗЫК sql IMMUTABLE;

СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ jsonb_array_castint (jsonb) RETURNS int [] AS $ f $
    SELECT array_agg (x) :: int [] || ARRAY [] :: int [] FROM jsonb_array_elements_text ($ 1) t (x);
$ f $ ЯЗЫК sql IMMUTABLE;
 

В каждом из них, объединившись с пустым массивом, они обрабатывают случай, из-за которого я немного разбил мозг, в том случае, если вы попробуете создать пустой массив из json /< code> jsonb , вы не получите ничего, а не пустой массив ( {} ), как и следовало ожидать. Я уверен, что для них есть какая-то оптимизация, но они оставлены как есть для простоты объяснения концепции.

ответил Joel B 28 ThuEurope/Moscow2017-12-28T22:44:49+03:00Europe/Moscow12bEurope/MoscowThu, 28 Dec 2017 22:44:49 +0300 2017, 22:44:49

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132