在Postgres中查询jsonb数组

尼哈尔·夏尔马

桌子：

CREATE TABLE appointment
(
  id bigserial NOT NULL,
  date_of_visit timestamp without time zone NOT NULL,
  symptoms text[],
  diseases text[],
  lab_tests text[],
  prescription_id bigint NOT NULL,
  medicines jsonb,
  CONSTRAINT appointment_pkey PRIMARY KEY (id),
  CONSTRAINT appointment_prescription_id_fkey FOREIGN KEY (prescription_id)
  REFERENCES prescription (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
 )
 WITH (
  OIDS=FALSE
 );

插入语句：

INSERT INTO appointment values(
    1,
    now(),
    '{"abc","def","ghi"}',
    '{"abc","def","ghi"}',
    '{"abc","def","ghi"}',
    1,
    '[{"sku_id": 1, "company": "Magnafone"}, {"sku_id": 2, "company": "Magnafone"}]')

我正在尝试针对postgres中的jsonb数组类型列进行查询。我手头有一些解决方案，如下所示。莫名其妙，它不起作用错误是- Cannot extract elements from a scalar。

SELECT distinct(prescription_id)
FROM  appointment
WHERE to_json(array(SELECT jsonb_array_elements(medicines) ->>'sku_id'))::jsonb ?|array['1'] 
LIMIT 2;

更新：查询运行正常。由于其他行中的某些行未运行，因此该列中存在一些不需要的值。

克林

表中的行包含列medicines而不是数组的标量值。您应该检查并正确更新数据。您可以通过以下查询找到这些行：

select id, medicines
from appointment
where jsonb_typeof(medicines) <> 'array';

或者，您可以在查询的此列中检查值的类型：

select prescription_id
from (
    select distinct on (prescription_id)
        prescription_id, 
        case 
            when jsonb_typeof(medicines) = 'array' then jsonb_array_elements(medicines) ->>'sku_id' 
            else null 
        end as sku_id
    from appointment
    ) alias
where sku_id = '1'
limit 2;

或简单地排除以下非数组值where clause：

select prescription_id
from (
    select distinct on (prescription_id)
        prescription_id, 
        jsonb_array_elements(medicines) ->>'sku_id' as sku_id
    from appointment
    where jsonb_typeof(medicines) = 'array'
    ) alias
where sku_id = '1'
limit 2;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。