Pratik Pratik - 1 year ago 57
JSON Question

Search WHERE value is present in one of the JSON array using only regular expression

My column looks something like this


It is a JSON field but we are still on postgres 9.2 so there is no JSON support for search.

I have to get all records where addId is either 28947 or 57818

Please help

Answer Source

Here's a regular expression that matches addId\":[ followed by any number of , and decimal digits with your magic numbers in the middle, ending in ].

SELECT regexp_matches(json_column, 'addId\\":\[[0-9,]*(28947|57818)[0-9,]*\]', 'g');

Tested it on your input data at this site. Did not test it with the actual regexp_matches function

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download