CODEWITHSUNDEEP
arraysjsonpostgresql
rweiser
rweiser

Reputation: 329

PostgreSQL - selecting JSON rows that have an array with a given element

I have a PostgreSQL table named data_audit with a column named body, which is of type json. Each row contains a value with the following structure:

{
  "target": {
    "ids": [
      "ID1",
      "ID2"
    ]
  }
}

(I've removed lots of irrelevant fields.)

I want to select all the rows whose ids array (which may contain 0, 1 or more strings) includes a particular value.

I've tried various combinations of ::jsonb[], ANY(), @>, json_array_elements_text() and more, but to no avail.

What's the simplest, most efficient way to run this query?

This table is not yet in production, so if it would be easier / more efficient if the field were of another type (jsonb?), that is potentially an option.

I'm running PostgreSQL 10.3.

Thanks!

Upvotes: 0

Views: 345

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

This is one appropriate way to query something you want.

select * FROM data_audit cross join lateral 
json_array_elements_text (body->'target'->'ids')  as j(id)
where j.id ='ID1';

Demo

Upvotes: 1

Related Questions