How to turn a json array into rows in postgres

Question

I have a json array stored in my postgres database. The json looks 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 SELECT the array so that any element is in a different row of the query result. So the SELECT statement I perform must return the data 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.

Answer 1

I post the answer originally written by pozs in the comment section.

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 where the documentation for v9.4 can be found.

Answer 2

More difficult example:

Suppose you have a table with rows containing jsonb array each and you wish to splat (or unnest) all that arrays and do some aggregate calculations on records contained in them.

Table (let it be categories):

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

So, if you want to count, how many required specifics you have, you will need to use such query:

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; 

Here FROM x, function(x.column) is a shortened form of a lateral join which effectively joins every row from categories with virtual table created by jsonb_to_recordset function from jsonb array in that same row.

And result will be:

 name  | total ---------------  Brand |     1 

Link to DB Fiddle: https://www.db-fiddle.com/f/c4xZcEgg9dsPVDtE7Keovv/0