主页 / dba / 问题 / 294061
Accepted
Dimitrios Desyllas
Asked: 2021-06-11 01:50:40 +0800 CST

如何在由 JSON 数组组成的 json 中搜索到对象？

  • 772

Ι 有下表命名discounts：

id Serialize
discount INT
offers JSON

有以下记录

ID 折扣 优惠
1 10 [{"type":"discount","percent":100},{"type":"freebie","id:100}]
2 0 [{"type":"discount","percent":100},{"type":"freebie","id:100}]
2 100 无效的

我如何在表中搜索所有具有非零折扣但在 JSON 中具有 JSON 类型的记录discount。

到目前为止，我已经尝试过这个查询：

select * 
from discounts r 
where discount <> 0 
  and offers @> '[{"type":"discount"}]'::json;

但我收到以下错误：

错误：运算符不存在：json @> json

提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。

职位：68

postgresql

1 个回答

  1. Best Answer
    CREATE TABLE discounts (id INT, discount INT, offers JSON);
INSERT INTO discounts VALUES
(1,   10, '[{"type":"discount","percent":100},{"type":"freebie","id":100}]'::JSON),
(2,   0,  '[{"type":"discount","percent":100},{"type":"freebie","id":100}]'::JSON),
(2,   100,    NULL);
SELECT * FROM discounts;
    ID 折扣 优惠
    1 10 [{"type":"discount","percent":100},{"type":"freebie","id":100}]
    2 0 [{"type":"discount","percent":100},{"type":"freebie","id":100}]
    2 100 无效的 
    select * 
from discounts r 
where discount <> 0 
  and offers @> '[{"type":"discount"}]'::json;

    错误：运算符不存在：json @> json
第 4 行：并提供 @> '[{"type":"discount"}]'::json;
                     ^
提示：没有运算符与给定的名称和参数类型匹配。您可能需要添加显式类型转换。

    select * 
from discounts r 
where discount <> 0 
  and offers::JSONB @> '[{"type":"discount"}]'::JSONB;
    ID 折扣 优惠
    1 10 [{"type":"discount","percent":100},{"type":"freebie","id":100}]

    db<>在这里摆弄

    • 1

相关问题