获取Postgres中的JSON数据

Question

我有这个数据在我的Postgres DB保存为TEXT，我需要得到值的详细信息时，ID=4417907656850和更新到一个单独的列。

我怎么能访问它，并得到值列时，如上所述的ID ?

[ 
{"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} 
]

Answer 1

您可以使用json_to_recordset:

从JSON访问数据

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)

Answer 2

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;

小提琴:https://dbfiddle.uk/?rdbms=postgres_11&小提琴= 8 d71aa125ea696e8cf5d2df177c1be13

注意:如果您愿意，可以使用横向而不是普通的表表达式来打开数组

Answer 3

您可以使用JSON路径表达式:

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