0

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!

rweiser
  • 319
  • 1
  • 3
  • 13

1 Answers1

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

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Thanks. Will performance of that query be as horrendous as it looks like it would be? – rweiser Mar 04 '19 at 11:08
  • @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](https://stackoverflow.com/a/36076895/7998591) – Kaushik Nayak Mar 04 '19 at 11:11
  • 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...`. – rweiser Mar 05 '19 at 11:05
  • @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 https://stackoverflow.com/a/3918601/7998591 – Kaushik Nayak Mar 05 '19 at 11:21