Postgresql: Find values in JSON array by wildcard and comparison operators with index
I know its been a while but I was just chugging on something similar (using wild cards to query json datatypes) and thought I'd share what I found.
Firstly, this was a huge point in the right direction:http://schinckel.net/2014/05/25/querying-json-in-postgres/
The take away is that your method of exploding the json element into something else (a record-set) is the way to go. It lets you query the json elements with normal postgres stuff.
In my case:
#Table:test ID | jsonb_column 1 | {"name": "", "value": "reserved", "expires_in": 13732} 2 | {"name": "poop", "value": "{\"ns\":[\"Whaaat.\"]}", "expires_in": 4554} 3 | {"name": "dog", "value": "{\"ns\":[\"woof.\"]}", "expires_in": 4554} Example Query
select * from test jsonb_to_recordset(x) where jsonb_column->>'name' like '%o%';# => Returns# 2 | {"name": "poop", "value": "{\"ns\":[\"Whaaat.\"]}", "expires_in": 4554}And to answer your question about jsonb: It looks like jsonb is the better route MOST of the time. It has more methods and faster read (but slower write) times.
Sources:
- http://www.postgresql.org/docs/9.4/static/functions-json.html
- http://www.postgresql.org/docs/9.4/static/datatype-json.html
Happy hunting!