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