0

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?

2

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.