Decomposing jsonb field

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    |[{"name": "Saturday", "scope": "system"},|
|    |                 | {"name": "favorite", "scope": "user"}]  |
+----------------------+-----------------------------------------+     
| 2  | paint the fence |[{"name": "Monday", "scope": "system"}]  |
+----------------------+-----------------------------------------+

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

+----+-----------------+------------+-----------+
| ID |  Name           | Tag Name   | Tag Scope |
+----------------------+------------+-----------+
| 1  | mow the lawn    | Saturday   | system    |
+----------------------+------------+-----------+
| 1  | mow the lawn    | favorite   | user      |
+----------------------+------------+-----------+     
| 2  | paint the fence | Monday     | system    |
+----------------------+------------+-----------+

But I'm having a hard time decomposing the jsonb field. Any ideas?

Answer 1

You need to unnest the array then extract the keys as columns from the resulting JSON values:

select td.id, td.name, tg.tag_data ->> 'name' as tag_name, tg.tag_data ->> 'scope' as scope
from todos td
  cross join lateral jsonb_array_elements(td.tags) as tg(tag_data)
order by td.id;

Online example: http://rextester.com/DWYIJ81620