How to turn a json array into rows in postgres

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.

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 totalFROM   categories,   jsonb_to_recordset(categories.specifics) AS specs(name jsonb, required boolean)WHERE   specs.required = TRUE  -- AND any other restrictions you needGROUP BY specs.nameORDER 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

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.