A line in my JSON column looks something like this:
{"general": {
"somekey": "somevalue",
"tags": ["first_tag", "second_tag", "third_tag"]}}
And I need to return lines with tags list that contains certain tag (e.g. "first_tag"). Is there a way to do this in PostgreSQL 9.3?
Assuming that the table is called t and the column is called x:
SELECT *
FROM t
WHERE exists(
SELECT 1
FROM json_array_elements(x#>'{general,tags}')
WHERE array_to_json(array[value])->>0='first_tag'
);
This does not use jsonb or other newer stuff so it should work on 9.3. See also sqlfiddle.
The idea is to use the json_array_elements function, which converts a json array into a sql table. That table has a single column called value of type json.
In this case, we use json_array_elements in a subquery to iterate through the list of all tags. One complication is that value (which here represents a tag) is of type json, so we have to convert it to a text. Unfortunately, postgresql doesn't have a function to do that directly, so we have to convert it into a single element array and then extract that single element as text. Then we can compare that text with the tag we are look for (first_tag in the above example). The result of this column is not significant, we are only interested in whether it is empty or not.
x.You can use:
CREATE OR REPLACE FUNCTION fn_json_array_contains(a_json json, a_e anyelement)
RETURNS BOOLEAN AS $BODY$
BEGIN
RETURN to_json(a_e)::TEXT IN (SELECT value::TEXT FROM json_array_elements(a_json) e);
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE
;
SELECT * FROM t WHERE fn_json_array_contains(x#>'{general,tags}', 'first_tag');
