Querying a jsonb array in postgres

Question

Table:

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 statement:

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"}]')

I am trying to query against a jsonb array type column in postgres. I had some solution in hand which is as below. Somehow it is not working The error is - 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;

Update: The query runs just fine. There was some unwanted value in the column for some other rows because of which it was not running.

Answer 1

There are rows in the table containing a scalar value in column medicines instead of array. You should inspect and properly update the data. You can find these rows with this query:

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

Alternatively, you can check the type of values in this column in the query:

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;

or simply exclude non-array values in 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;