Decomposing jsonb field 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?
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 -&gt;&gt; 'name' as tag_name, tg.tag_data -&gt;&gt; '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