BanzaiTokyo BanzaiTokyo - 3 months ago 29
JSON Question

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?

Answer

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. If we have such a table, we can then search it using the standard sql WHERE in a subquery.