How to extract data from a jsonb column
Data Table
Id | Result |
---|---|
1001 | {"Green": {"value": "250.3", "reading": "250.3"}, "MbSampleType": {"value": "Water"}} |
1002 | {"Green": {"value": "0", "reading": "0"}, "Yellow": {"value": "560", "reading": "560"}, "TotalVibrio": {"value": "560", "reading": "560"}, "MbSampleType": {"value": "water"}}} |
Result should come out as follows
id | key_value | value |
---|---|---|
1001 | Green | 250.3 |
1001 | MbSampleType | Water |
1002 | Green | 0 |
1002 | Yellow | 560 |
1002 | TotalVibrio | 560 |
1002 | MbSampleType | Water |
where key_value = 'Green'
CodePudding user response:
select result -> 'green' as key_value
from table
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/