Search code examples
jsonpostgresqlpostgresql-9.3

Query Postgres 9.3 JSON to check if array contains a string?

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?

Solution

  • 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.