Преобразование массива строк в массив объектов Json в Postgresql Jsonb

у меня есть столбец jsonb, например

{
    "foo":{"bar" :["b","c","d"]}
}

Что я хочу преобразовать все элементы массива «bar» в json с ключом «name», результат должен быть:

{
    "foo":{"bar" :[{"name":"b"},{"name":"c"},{"name":"d"}]}
}

Я хочу сделать это, используя запросы postgresql во всех строках моей таблицы.

Я пробовал различные запросы и функции, но моя основная проблема заключается в переносе части моих строк.

Советы и помощь приветствуются


postgresql sql jsonb postgresql-9.5
person Nil Null    schedule 23.09.2019    source источник
comment
["name":"b","name":"c","name":"d"] недействителен, потому что это не объектная нотация. Также не допускаются одинаковые ключи в объекте   -  person Joven28    schedule 23.09.2019
comment
Вы действительно используете Postgres 9.1? Похоже, что родная поддержка JSON не добавлялась до версии 9.2, а JSONB — до версии 9.4.   -  person ravioli    schedule 23.09.2019
comment
И jsonb не добавлялся до версии 9.5.   -  person a_horse_with_no_name    schedule 23.09.2019

Ответы (1)


arrow_upward
1
arrow_downward

Предполагая, что вы используете версию Postgres с поддержкой JSON (9.4+), это должно помочь вам:

WITH cte(myJSON) AS (
  SELECT CAST('{"foo":{"bar" :["b","c","d"]}}'AS JSONB) AS MyJSON 

  UNION ALL

  SELECT CAST('{"foo":{"bar" :["e","f","g"]}}'AS JSONB) AS MyJSON  
) 

SELECT
  JSON_AGG(
    (SELECT ROW_TO_JSON(_) FROM (SELECT name) AS _)
  ) myrow
FROM (
  SELECT JSONB_ARRAY_ELEMENTS(myjson->'foo'->'bar') AS name,
    ROW_NUMBER() OVER() AS RowNum
  FROM cte
) src
GROUP BY src.RowNum

Это вернет [{"name":"b"},{"name":"c"},{"name":"d"}].

Затем вы можете создать свой окончательный JSON по мере необходимости.

SQL Fiddle
Ссылка

Обновить
Это очень хакерский способ, и я уверен, что есть более чистый способ, но я обновил запрос выше, чтобы он обрабатывал несколько строк. Просто замените ссылки CTE на фактическое имя таблицы, а myjson на имя столбца JSON.

Выход:

[{"name":"b"},{"name":"c"},{"name":"d"}]
[{"name":"e"},{"name":"f"},{"name":"g"}]

Дай мне знать, если это работает.

Обновленное обновление
Вот оператор UPDATE, который вы можете использовать:

UPDATE t1 tgt
SET jsoncol = JSONB_SET(
  jsoncol, -- Source JSON
  '{foo,bar}', -- Target node to update
  src.new_json -- New value 
)
FROM (
  SELECT 
    ID, 
    JSONB_AGG(
      (SELECT TO_JSONB(_) FROM (SELECT name) AS _) -- Convert new row to JSON
    ) new_json
  FROM (
    SELECT 
      ID, 
      JSONB_ARRAY_ELEMENTS(jsoncol->'foo'->'bar') AS name -- Convert array to rows
    FROM t1
  ) src
  GROUP BY src.ID
) src
WHERE tgt.ID = src.ID -- Update "tgt" table with rows from "src" table
;

DB-Fiddle

person ravioli    schedule 23.09.2019
comment
Он работает только для одной строки, есть идеи, как я могу перебрать все строки? - person Nil Null; 23.09.2019
comment
Это работает, но теперь я столкнулся с другой проблемой: я обновляю эту таблицу, которая должна измениться только на полосе, есть подсказки о том, как написать обновление? - person Nil Null; 23.09.2019
comment
Вы можете использовать что-то вроде этого: UPDATE MyTable SET jsoncol = JSONB_SET(jsoncol, '{foo,bar}', <new_JSON_value>). Это заменит значение в foo->bar на new_JSON_value. Если вы хотите использовать приведенный выше запрос, вам нужно выполнить UPDATE FROM и присоединить таблицу src к таблице tgt. Что такое PK таблицы, которую вы хотите обновить? - person ravioli; 23.09.2019
comment
Он имеет идентификатор в качестве первичного ключа. Я попробовал ваш подход, но он копирует все строки - person Nil Null; 23.09.2019
comment
что вы подразумеваете под таблицей tgt? - person Nil Null; 23.09.2019
comment
tgt = целевая таблица, src = исходная таблица. См. выше. Дайте мне знать, если это работает - person ravioli; 23.09.2019