k4ppa k4ppa - 2 months ago 19
JSON Question

How to expand a json array into rows in postgres

I have a json array stored in my postgres database.
The json look like this:

[
{
"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"
}
]


Now I need to unwrap the array so that any element is in a different row, in this way:

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"}


I tried using the
unnest()
function

SELECT unnest(json_data::json)
FROM my_table


but it doesn't accept the
jsonb
type

How can I do this work?

Answer

I post the aswers originally written by pozs in the comment.

unnest() is for PostgreSQL's array types.

Instead one of the following function can be used:

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

Example:

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

output value

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

Here the documentation for v9.4 can be found.

Comments