我如何获得JSON对象在指定的路径- postgresql

Question

我试图在指定路径的JSON对象内获取元素item_sku。

我有这个列，我想只提取item_sku添加到一个列使用postgresql。我该怎么解决这个问题呢?

这是我正在尝试的代码:

select o.reference,
o.id as "ord_id",
o.created_at,
o.aasm_state,
o.payment_details -> 'payment_method' as "payment_method", 
max(gr.updated_at) as "last_updated_at", 
o.shipping_address -> 'country' as "country",
(gr.request_body -> 0 #>> '{lines}') as ESD

下面是我得到的结果:

[{"item_sku": "60997070103", "image_url": "https://hahaha", "identifier": "7713128"}, {"item_sku": "60997070103", "image_url": "https://hahha", "identifier": "7713131"}, {"item_sku": "62007100402", "image_url": "https://hahha", "identifier": "7713129"}]

这是数据从我试图得到的信息:

[{"lines": [{"item_sku": "60997070103", "image_url": "https://hahaha", "identifier": "7713128"}, {"item_sku": "60997070103", "image_url": "https://hahha", "identifier": "7713131"}, {"item_sku": "62007100402", "image_url": "https://hahha", "identifier": "7713129"}]

Answer 1

SELECT gr.request_body #>> '{0,lines,0,item_sku}'将导致"60997070103">

SELECT gr.request_body #>> '{0,lines,1,item_sku}'将导致"60997070103">

SELECT gr.request_body #>> '{0,lines,2,item_sku}'将产生"62007100402">

SELECT elt->>'item_sku' AS item_sku
FROM jsonb_array_elements(gr.request_body#>'{0,lines}') AS elt 

将导致

tbody> <<tr>

item_sku
60997070103
60997070103
62007100402