Posted on 2019-10-06

Postgres jsonb_to_recordset join

(from https://stackoverflow.com/questions/36174881/how-to-turn-a-json-array-into-rows-in-postgres)

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.