Postgresql查询嵌套JSONB字段中的对象
发布时间:2020-12-13 16:17:16 所属栏目:百科 来源:网络整理
导读:我正在使用PostgreSQL 9.6,我有一个名为“ItemDbModel”的表,其中有两列如下: No integer,Content jsonb 说我把许多记录像: "No": 2,{"obj":"x","Item": {"Name": "BigDog","Model": "NamedHusky","Spec":"red dog"}} "No": 4,"Item": {"Name": "MidDog","
我正在使用PostgreSQL 9.6,我有一个名为“ItemDbModel”的表,其中有两列如下:
No integer,Content jsonb 说我把许多记录像: "No": 2,{"obj":"x","Item": {"Name": "BigDog","Model": "NamedHusky","Spec":"red dog"}} "No": 4,"Item": {"Name": "MidDog","Model": "NamedPeppy","Spec":"no hair"}} "No": 5,"Item": {"Name": "BigCat","Model": "TomCat","Spec":"blue color"}} 如何查询表格: >记录“Content.Item.Name”包含“Dog”和“Content.Item.Spec”包含“red”的记录. 并按“Content.Item.Name.length”排序? 谢谢!
你应该熟悉
JSON Functions and Operators.
-- #1 select * from example where content->'Item'->>'Name' ilike '%dog%' and content->'Item'->>'Spec' ilike '%red%' -- #2 select * from example where content->'Item'->>'Name' ilike '%dog%' or content->'Item'->>'Spec' ilike '%red%' -- #3 select distinct on(no) t.* from example t,lateral jsonb_each_text(content->'Item') where value ilike '%dog%'; -- and select * from example t order by length(content->'Item'->>'Name'); (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |