2023-02-10

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'



No comments:

Post a Comment