Sample Header Ad - 728x90

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