Get JSON data in Postgres

Question

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} 
]

Andrew · Accepted Answer · 2022-05-17 14:14:54Z

1

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)

answered May 17, 2022 at 14:14

Andrew

1757 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Convel · Accepted Answer · 2022-05-17 14:23:07Z

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

user330315 · Accepted Answer · 2022-05-17 14:25:04Z

0

You can use a JSON path expression:

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

answered May 17, 2022 at 14:25

user330315

Collectives™ on Stack Overflow

Get JSON data in Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related