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

У меня есть json-массив, хранящийся в моей базе данных postgres. Json выглядит так:

    [
        {
            "operation": "U",
            "taxCode": "1000",
            "description": "iva description",
            "tax": "12"
        },
        {
            "operation": "U",
            "taxCode": "1001",
            "description": "iva description",
            "tax": "12"
        },
        {
            "operation": "U",
            "taxCode": "1002",
            "description": "iva description",
            "tax": "12"
        }
    ]

Теперь мне нужно выбрать массив, чтобы любой элемент находился в другой строке результата запроса. Поэтому оператор SELECT, который я выполняю, должен возвращать данные следующим образом:

 data
--------------------------------------------------------------------------------------
 { "operation": "U", "taxCode": "1000", "description": "iva description", "tax":"12"}
 { "operation": "U", "taxCode": "1001", "description": "iva description", "tax":"12"}
 { "operation": "U", "taxCode": "1002", "description": "iva description", "tax":"12"}

Я попытался использовать функцию unnest()

SELECT unnest(json_data::json)
FROM my_table

но он не принимает тип jsonb

Ответ 1

Я отправляю ответ, первоначально написанный pozs в разделе комментариев.

unnest() предназначен для типов массивов PostgreSQL.

Вместо этого можно использовать одну из следующих функций:

json_array_elements(json) (9.3 +)
jsonb_array_elements(jsonb) (9.4 +)
json[b]_array_elements_text(json[b]) (9.4 +)

Пример:

select * from json_array_elements('[1,true, [2,false]]')

выходное значение

 -------------
 | 1         |
 -------------
 | true      |
 -------------
 | [2,false] |
 -------------

Здесь, где можно найти документацию для v9.4.

Ответ 2

Я бы предложил использовать команду json_to_recordset в вашем случае. Ваш SQL должен быть таким:

select *
from json_to_recordset('[{"operation":"U","taxCode":1000},{"operation":"U","taxCode":10001}]')
as x("operation" text, "taxCode" int);

Вывод:

------------------------
|   |operation|taxCode |
------------------------
| 1 |   "U"   |   1000 |
------------------------
| 2 |   "U"   |  10001 |
------------------------

Столбцы (или ключи JSON) примера могут быть дополнительно расширены.

Ответ 3

Более сложный пример:

Предположим, у вас есть таблица со строками, каждый из которых содержит массив jsonb, и вы хотите разделить (или удалить) все эти массивы и выполнить некоторые совокупные вычисления для содержащихся в них записей.

Таблица (пусть будет categories):

 id | specifics (jsonb)
-----------------------------------------------------------------------------------
  1 | [{"name": "Brand", "required": true}, {"name": "Color", "required": false}]
  2 | [{"name": "Brand", "required": false}, {"name": "Color", "required": false}]

Итак, если вы хотите посчитать, сколько у вас необходимых данных, вам нужно будет использовать такой запрос:

SELECT specs.name, COUNT(*) AS total
FROM 
  categories, 
  jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)
WHERE 
  specs.required = TRUE
  -- AND any other restrictions you need
GROUP BY specs.name
ORDER BY total DESC;

Здесь FROM x, function(x.column) представляет собой сокращенную форму бокового соединения, которое эффективно объединяет каждую строку из categories с виртуальной таблицей, созданной функцией jsonb_to_recordset из массива jsonb в той же строке.