Select rows where items in a JSON array has a specific value

Question

I have a table with a JSONB column. The column contains a number of topics as an array: Example:

select id, topics from c;

   id    |                                                                 topics
---------+-----------------------------------------------------------------------------------------------------------------------------------------
 7783263 | [{"id": "ddded8f7-1a72-4e43-b040-86a01e82d2c6", "name": "Finance"}]
 7783556 | [{"id": "7bad8662-a07b-45c5-bea5-1aa6050c0dfb", "name": "Politics"}]
 7783795 |
 7785318 | [{"id": "7bad8662-a07b-45c5-bea5-1aa6050c0dfb", "name": "Politics"}, {"id": "ddded8f7-1a72-4e43-b040-86a01e82d2c6", "name": "Finance"}]

I have tried the @> operator and some others but to no help. I need to be able to select all the items that has either one specific topic like "Finance" or several such as ["Finance", "Politics"].

I tried topics @> '{"name": ["Finance"]}' as an example, but that didn't work.

This might get you started: stackoverflow.com/questions/22736742/… — Stewart Macdonald
– Stewart Macdonald, Commented Dec 14, 2022 at 12:17

user330315 · Accepted Answer · 2022-12-14 12:28:48Z

0

One way is to use an OR condition:

select *
from the_table
where topics @> '[{"name": "Finance"}]'
   or topics @> '[{"name": "Politics"}]'
;

If you are using Postgres 12 or later, you can also collect all names into a (JSON) array and use the ?| operator:

select *
from the_table
where jsonb_path_query_array(topics, '$.name') ?| array['Finance', 'Politics']

answered Dec 14, 2022 at 12:28

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select rows where items in a JSON array has a specific value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related