How to deconstruct a JSONB object

Posted on

Question :

In a PG10 database, I have a table called ToDos where I have defined a jsonb field called tags.

Records look like

+----+-----------------+--------------------------------------------+
| ID |  name           | tags                                       |
+----------------------+--------------------------------------------+
| 1  | mow the lawn    | '{"day": ["12", "15"], "month": ["7", "9"],| 
|                      |   "year": ["2018"]}'                       |
+----------------------+--------------------------------------------+     
| 2  | paint the fence |'{"day": ["4"], "month": ["11"], "year":    |
|                      | ["2018"]}'                                 |
+----------------------+--------------------------------------------+

Now I’m trying to write a query that returns something like

+----+-----------------+------------+-----------+
| ID |  name           | step       | magnitude |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Day        | 12        |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Day        | 15        |
+----------------------+------------+-----------+    
| 1  | mow the lawn    | Month      | 7         |
+----------------------+------------+-----------+    
| 1  | mow the lawn    | Month      | 9         |
+----------------------+------------+-----------+    
| 1  | mow the lawn    | Year       | 2018      |
+----------------------+------------+-----------+  
 .........
+----------------------+------------+-----------+  
| 2  | paint the fence | Year       | 2018      |
+----------------------+------------+-----------+

I have written the following query

SELECT aa.id, aa.name, ao.key AS step, ao.value AS magnitude
FROM todos aa
CROSS JOIN LATERAL (SELECT * FROM jsonb_each_text(tags)) AS ao
ORDER  BY aa.id;

Which returns

+----+-----------------+------------+-----------+
| ID |  name           | step       | magnitude |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Day        | [12,15]   |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Month      | [7,9]     |
+----------------------+------------+-----------+
  ......

But now I’m stuck. Any ideas how I can get a row for each magnitude?

Answer :

You need to first unnest the keys, then you can unnest the array elements.

select td.id, td.name, tg.step, m.magnitude
from todos td
  cross join jsonb_each(tags) as tg(step, vals)
  cross join jsonb_array_elements(tg.vals) as m(magnitude)
order by td.id, td.step;

Try this:

SELECT z.id, z.name, z.step, jsonb_array_elements(z.magnitude) 
FROM (
SELECT aa.id, aa.name, ao.key AS step, ao.value AS magnitude
FROM todos aa
CROSS JOIN LATERAL (SELECT * FROM jsonb_each(tags)) AS ao) z
ORDER  BY z.id;

Leave a Reply

Your email address will not be published. Required fields are marked *