Get JSON data in Postgres-CodePudding

I have this data in my Postgres DB saved as TEXT ,I need to get the details of value when the ID=4417907656850 and update it into a separate column.

How can i access it and get the value column when the ID is mentioned as above?

[ 
{"id": 23581047, "value": null}
, {"id": 23776117, "value": false}
, {"id": 23772413, "value": false}
, {"id": 4441053996050, "value": null}
, {"id": 4417907677458, "value": "cr_shipments"}
, {"id": 360020702020, "value": null}
, {"id": 360011112800, "value": true}
, {"id": 4417907656850, "value": "shipments__external__carrier__return_to_sender"}
, {"id": 360011108000, "value": ["return"]}
, {"id": 360011112620, "value": false}
, {"id": 360011112640, "value": false} 
]

CodePudding user response：

You can access data from JSON by using json_to_recordset:

select *
from json_to_recordset('[ 
{"id": 23581047, "value": null}
, {"id": 23776117, "value": false}
, {"id": 23772413, "value": false}
, {"id": 4441053996050, "value": null}
, {"id": 4417907677458, "value": "cr_shipments"}
, {"id": 360020702020, "value": null}
, {"id": 360011112800, "value": true}
, {"id": 4417907656850, "value": "shipments__external__carrier__return_to_sender"}
, {"id": 360011108000, "value": ["return"]}
, {"id": 360011112620, "value": false}
, {"id": 360011112640, "value": false} 
]'::json)
as x(id bigint, value json)

CodePudding user response：

CREATE TABLE foo (
    id serial NOT NULL PRIMARY KEY,
    bar json NOT NULL
);

INSERT INTO foo (bar)
VALUES('[ 
{"id": 23581047, "value": null}
, {"id": 23776117, "value": false}
, {"id": 23772413, "value": false}
, {"id": 4441053996050, "value": null}
, {"id": 4417907677458, "value": "cr_shipments"}
, {"id": 360020702020, "value": null}
, {"id": 360011112800, "value": true}
, {"id": 4417907656850, "value": "shipments__external__carrier__return_to_sender"}
, {"id": 360011108000, "value": ["return"]}
, {"id": 360011112620, "value": false}
, {"id": 360011112640, "value": false} 
]
');

WITH cte AS (
   SELECT json_array_elements(bar) AS json FROM foo
) 

SELECT 
   * 
FROM 
   cte 
WHERE 
   CAST (json ->> 'id' AS BIGINT) = 4417907656850;

fiddle: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8d71aa125ea696e8cf5d2df177c1be13

Note: I believe you can use lateral instead of a common table expression if you prefer in order to unnest the array

CodePudding user response：

You can use a JSON path expression:

select jsonb_path_query_first(the_column::jsonb, '$[*] ? (@.id == 4417907656850)') ->> 'value'
from the_table;