CODEWITHSUNDEEP
sqlpostgresqljsonb
Robert Henderson
Robert Henderson

Reputation: 1

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

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.

Upvotes: 0

Views: 1189

Answers (1)

user330315
user330315

Reputation:

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']

Upvotes: 0

Related Questions