Querying values jsonB in Postgresql
I have a table with ProductID (int) and ProductGroups jsonb. The ProductGroups just have values in the json rather than tag names. I want to be able to query the following data to get ProductID where ProductGroup contains 69.
ProductID ProductGroups
125481 [134, 83]
128166 [134, 83]
128175 [134, 83]
128172 [134, 83]
131492 [69, 134]
131489 [69, 134]
131860 [128, 131, 133, 100, 71]
128142 [134, 83]
I have queried what I think of as normal jsonb with tag names in different tables with below query where I callout the name and value
SELECT * FROM trans."TxnHeader" mpt, jsonb_array_elements(mpt."ExtensionProperty") as ext where 1=1 and jsonb_typeof(mpt."ExtensionProperty") = 'array' and ext->>'Name' = 'posTranId' and ext->>'Value' = '8539'
Comments
Post a Comment