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'
select result -> 'green' as key_value
from table
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/