1
[
  {
    "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>

2
  • 1
    Your example is not valid JSON, for multiple reasons. Also, it is isn't clear what output you want.
    – jjanes
    Jan 26, 2023 at 18:44
  • I have fixed the example and added more details regarding the output I want
    – Don Draper
    Jan 26, 2023 at 18:58

1 Answer 1

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'
    )
);
1
  • The first solution is elegant, thank you. Would it work with ANY though? I just realized I might want to test for several values
    – Don Draper
    Jan 27, 2023 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.