Pratik Pratik - 1 month ago 5
JSON Question

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

My column looks something like this

"{"a1":"{\"name\":[{\"id\":32,\"addId\":[28947,33268,48148,1594,57818,57819],\"hasPhone\":true}],\"phone\":[{\"phoneId\":5,\"selectionMode\":true}],\"phoneType\":[1],\"selectionMode\":\"GROUP\"}"}"


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

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

Comments