I have a JSON with the following structure:
uid | item
1 |[{"id":1, "m":"123", "s":1},{"id":2, "msg":"on","s":2},{"id":3, "msg":"of","s":1}]
2 |[{"id":1, "m":"yes", "s":2},{"id":3, "msg":"gh","s":0}]
3 |[{"id":1, "m":"qa", "s":1},{"id":4, "msg":"ks"},{"id":5, "m":"test"}]
I want to query this table based on id
and get rows based on matched object values. For e.g. for id=3
I want:
uid | id | m | s
1 | 3 | of | 1
2 | 3 | gh | 0
This table has over 500M rows so I would need an index. Using GIN(item jsonb_path_ops)
with item @> '[{"id": 3}]'
works, but I don't know how to get the exact json object from array that matched.
I can turn the JSON structure to:
{
"1": { "m":"123", "s": 1 },
"2": { "m":"on", "s":2 },
"3": { "m":"of", "s": 1 }
}
and use GIN(item)
and check for key exists with ?
and then try to fetch the key value. But the index size might increase which might be an overkill given that I only want to search based on id.
Maybe I can use a B-Tree index but not sure how. What should be my JSON structure for most efficient querying? and what index should be used so that I get only the matched object from the array?
Some facts:
- Cardinality of
id
<<<uid
. (id = ~100,000, uid = ~500,000,000) - Normalizing this turns this table to 500M*10,000 rows which is extremely slow.
- Each JSON array will have upto 50 objects only.