我有一个表,我想搜索
JSON数组数据.
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}]');
如this answer所述,我创建了一个函数,它还允许在数组数据上创建索引(重要).
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;
如果我想找到一个完整的值(例如“Value B1”),这很好用：
SELECT *
FROM data
WHERE '{"Value B1"}'::text[] <@ (json_val_arr(json,'name'));
现在我的问题：
>是否可以使用通配符查找值(例如“Value *”)？像以下(天真)方法的东西：
...
WHERE '{"Value%"}'::text[] <@ (json_val_arr(json,'name'));
>是否可以使用比较运算符查找数值(例如> = 10)？再次,一个天真的,明显错误的方法：
...
WHERE '{10}'::int[] >= (json_val_arr(json,'value'));
我试图创建一个返回int []的新函数但是没有用.
我创建了一个SQL Fiddle来说明我的问题.
SELECT * FROM data,json_array_elements(json) jsondata WHERE jsondata ->> 'name' LIKE 'Value%';
和
... WHERE cast(jsondata ->> 'value' as integer) <= 10;
另外,我想最终在Postgresql 9.4中使用JSONB实现所有这些,但我认为对于上述问题,这应该不是问题.
非常感谢你！
我知道它已经有一段时间,但我只是在玩类似的东西(使用通配符查询json数据类型),并认为我会分享我发现的东西.
首先,这是朝着正确方向迈出的一大步：
http://schinckel.net/2014/05/25/querying-json-in-postgres/
需要注意的是,将json元素爆炸成其他东西(记录集)的方法是可行的.它允许您使用正常的postgres内容查询json元素.
就我而言：
#Table:test
ID | jsonb_column
1 | {"name": "","value": "reserved","expires_in": 13732}
2 | {"name": "poop","value": "{\"ns\":[\"Whaaat.\"]}","expires_in": 4554}
3 | {"name": "dog","value": "{\"ns\":[\"woof.\"]}","expires_in": 4554}
示例查询
select * from test jsonb_to_recordset(x) where jsonb_column->>'name' like '%o%';
# => Returns
# 2 | {"name": "poop","expires_in": 4554}
并回答你关于jsonb的问题：看起来jsonb是当时最好的路线.它有更多的方法和更快的读取(但写入速度更慢)时间.
资料来源：
> http://www.postgresql.org/docs/9.4/static/functions-json.html
> http://www.postgresql.org/docs/9.4/static/datatype-json.html
快乐狩猎！
