I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like:
No integer,
Content jsonb
Say I put many records like:
"No": 2, {"obj":"x","Item": {"Name": "BigDog", "Model": "NamedHusky", "Spec":"red dog"}}
"No": 4, {"obj":"x","Item": {"Name": "MidDog", "Model": "NamedPeppy", "Spec":"no hair"}}
"No": 5, {"obj":"x","Item": {"Name": "BigCat", "Model": "TomCat", "Spec":"blue color"}}
How can I query the table for:
- Records where "Content.Item.Name" contains "Dog" And "Content.Item.Spec" contains "red".
- Records where "Content.Item.Name" contains "Dog" OR "Content.Item.Spec" contains "red".
- Records where Any json fields in "Content.Item" contains "dog".
And order by "Content.Item.Name.length"?
Thank you!