Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a table with JSON array data I'd like to search.

CREATE TABLE data (id SERIAL, json JSON);

INSERT INTO data (id, json) 
  VALUES (1, '[{"name": "Value A", "value": 10}]');

INSERT INTO data (id, json) 
  VALUES (2, '[{"name": "Value B1", "value": 5}, {"name": "Value B2", "value": 15}]');

As described in this answer, i created a function, which also allows to create an index on the array data (important).

CREATE OR REPLACE FUNCTION json_val_arr(_j json, _key text)
  RETURNS text[] AS
$$
SELECT array_agg(elem->>_key)
FROM   json_array_elements(_j) AS x(elem)
$$
  LANGUAGE sql IMMUTABLE;

This works nicely if I want to find an entire value (eg. "Value B1"):

SELECT *
FROM data
WHERE '{"Value B1"}'::text[] <@ (json_val_arr(json, 'name'));

Now my questions:

  1. Is it possible to find values with a wildcard (eg. "Value*")? Something like the following (naive) approach:

    ...
    WHERE '{"Value%"}'::text[] <@ (json_val_arr(json, 'name'));
    
  2. Is it possible to find numeric values with comparison operators (eg. >= 10)? Again, a naive and obviously wrong approach:

    ...
    WHERE '{10}'::int[] >= (json_val_arr(json, 'value'));
    

    I tried to create a new function returning int[] but that did not work.

I created a SQL Fiddle to illustrate my problem.

Or would it be better to use a different approach like the following working queries:

SELECT *
FROM data,
   json_array_elements(json) jsondata
WHERE jsondata ->> 'name' LIKE 'Value%';

and

...
WHERE cast(jsondata ->> 'value' as integer) <= 10;

However, for these queries, I was not able to create any index that was actually picked up by the queries.

Also, I'd like to implement all this in Postgresql 9.4 with JSONB eventually, but I think for the above questions this should not be an issue.

Thank you very much!

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.