How to filter rows based on a nested JSON array field?

Question

[
  {
    "id": 1,
    "columns": [
      {
        "id": 16,
        "expression": "foo"
      },
      {
        "id": 12,
        "expression": "bar"
      }
    ]
  },
  {
    "id": 2,
    "columns": [
      {
        "id": 11,
        "expression": "baz"
      },
      {
        "id": 8,
        "expression": "foobar"
      }
    ]
  }
]

I have a table where one of the columns is a JSON array of elements. Above you can see what this field value may look like.

The idea is to filter rows of the tables based on the value of the expression field. For example, I might want to only leave the rows where there is a column where expression equals foo.

My first idea was to use json_array_elements() multiple times and then write a WHERE EXISTS(SELECT true FROM ...) clause but this results in row duplicates. In addition, my attempts at combining several json_array_elements don't always work the way I expect them to.

It would work if I could somehow flatten all items so that there is one large list of expression values for each row. Unfortunately, I am not sure whether there is a convenient way to do that with this nested structure.

UPDATE: fixed the JSON object

My table looks like this:

id | description |      | tables
1  | sample_description | <sample_above>

The desired result is to keep rows where there exists an item in the columns field among items of this tables json array such that the expression value equals <some_value>

Answer 1

The WHERE EXISTS with json_array_elements certainly would work, and wouldn't generate duplicate rows when done correctly. But it also can't benefit from the gin index on the json column. It would be better to do it in way which can, such as this use of the containment operator.

select * from json_table where x @> '[{"columns":[{"expression":"foo"}]}]';

It will also work with ANY, although it is rather verbose:

 select * from json_table where x @> ANY(ARRAY['[{"columns":[{"expression":"foo"}]}]'::jsonb,'[{"columns":[{"expression":"foot"}]}]']);

An example of the working but suboptimal WHERE EXISTS:

select * from json_table where exists (
    select 1 from jsonb_array_elements(x) f(x2) where exists (
        select 1 from jsonb_array_elements(x2->'columns') ff(x3) where x3->>'expression' = 'foo'
    )
);