Find rows containing a key in a JSONB array of records
12
votes
2
answers
49462
views
I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
"array": [ {"name":"bla1", "attr": "somevalue"},
{"name":"bla2", "otherdata": "somevalue2"},
{"name":"bla3", "otherdata": "somevalue"}
],
"otherstuff": "stuff"
}
Now I do btree's on
jdata->'name'
or (jdata->'datetime')::cast
and that works great.
I also do json_path_ops where jdata->'array' @> '[{"name":"bla3"}]'
works a real charm.
My problem is the attr
key can be in any of the objects in the array and I care about the record if the key is present, however the value can be nearly anything. Is there a way to query for this? Is there a way it can be indexed? I want to do jdata->'array' @> '[{"attr": ?}]'
Or maybe the ? 'attr'
can be used inside an array somehow?
Currently I am thinking a trigger that scans for the key and then moves it to a header with a true or false or whatever, and then a normal btree will work. Is there a better way? I need to edit around 500k records at the average site to add this values.
Please point me in a direction.
Asked by Kobus
(149 rep)
Jan 30, 2018, 02:37 PM
Last activity: Jun 20, 2024, 12:10 PM
Last activity: Jun 20, 2024, 12:10 PM