CODEWITHSUNDEEP
jsonpostgresqlpostgresql-9.3
BanzaiTokyo
BanzaiTokyo

Reputation: 1404

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?

Upvotes: 1

Views: 895

Answers (2)

Israel Lins Albuquerque
Israel Lins Albuquerque

Reputation: 94

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');

Upvotes: 0

redneb
redneb

Reputation: 23850

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.

Upvotes: 1

Related Questions