我正在使用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”包含“Dog”或“Content.Item.Spec”包含“red”的记录.
>记录“Content.Item”中的任何json字段包含“dog”的位置.
并按“Content.Item.Name.length”排序?
谢谢!
你应该熟悉
JSON Functions and Operators.
原文链接:https://www.f2er.com/postgresql/192082.html-- #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');