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