PG::InvalidParameterValue: ERROR: 无法从标量中提取元素

Question

我使用以下查询从 JSON 列中获取数据。

SELECT id FROM ( SELECT id,JSON_ARRAY_ELEMENTS(shipment_lot::json) AS js2 FROM file_table WHERE ('shipment_lot') = ('shipment_lot') ) q WHERE js2->> 'invoice_number' LIKE  ('%" abc1123"%')

我的Postgresql版本是9.3

在 JSON 列中保存的数据：

[{ "id"=>2981, "lot_number"=>1, "activate"=>true, "invoice_number"=>"abc1123", "price"=>378.0}]

但是，我收到此错误：

ActiveRecord::StatementInvalid (PG::InvalidParameterValue: ERROR:  cannot extract element from a scalar:
SELECT id FROM 
( SELECT id,JSON_ARRAY_ELEMENTS(shipment_lot::json) 
AS js2 FROM file_heaps 
WHERE ('shipment_lot') = ('shipment_lot') ) q 
WHERE js2->> 'invoice_number' LIKE  ('%abc1123%'))

我如何解决这个问题。

Answer 1

您的问题是您存储了不正确的 JSON

如果您尝试在 postgres 上运行示例数据，它将无法运行

SELECT ('[{ "id"=>2981, "lot_number"=>1, "activate"=>true, "invoice_number"=>"abc1123", "price"=>378.0}]')::json

这是格式正确的 JSON：

SELECT ('[{ "id":2981, "lot_number":1, "activate":true, "invoice_number":"abc1123", "price":378.0}]')::json