How to turn a json array into rows in postgres

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.

Solution 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.

Solution 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

Solution 3:

I would suggest using the json_to_recordset command in your case. Your SQL should then be:

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

The output is:

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

The columns (or JSON keys) of the example can be freely further expanded.