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

Question

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!

Kaushik Nayak · Accepted Answer · 2019-03-04 10:18:43Z

1

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

answered Mar 4, 2019 at 10:18

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

4 Comments

rweiser Over a year ago

Thanks. Will performance of that query be as horrendous as it looks like it would be?

Kaushik Nayak Over a year ago

@rweiser : Not much. But, if you are truly worried about performance, you may either nomalize your tables by storing those values in different tables/columns or create an Index on the json attribute which is frequently fetched. See this example

rweiser Over a year ago

What do I gain by the cross join lateral? It seems to work just as well if I simply query ... FROM data_audit, json_array_elements_text....

Kaushik Nayak Over a year ago

@rweiser : Nothing.. CROSS JOIN is the ANSI-92 syntax and is more verbose. The implicit comma syntax a,b is outdated and should preferably be avoided stackoverflow.com/a/3918601/7998591

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related