Postgresql:通过带有索引的通配符和比较运算符在JSON数组中查找
我有一个表,我想搜索
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数据类型),并认为我会分享我发现的东西.
首先,这是朝着正确方向迈出的一大步: 需要注意的是,将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 快乐狩猎! (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |