Postgres jsonb_array_elements() returns "cannot extract elements from a scalar", even when given a valid JSON array?

Question

I have a table in postgres called day, which contains a jsonb column called plan_activities.

I have a day record with day.id = 18 and plan_activities contains the following JSON:

[
  {
    "activity": "Gym",
    "cardio": false,
    "strength": true,
    "quantity": 20,
    "units": "mins",
    "timeOfDay": "Evening",
    "summary": "Gym - 20 mins - Evening",
    "timeOfDayOrder": 4
  },
  {
    "activity": "Walk",
    "cardio": true,
    "strength": false,
    "quantity": 15,
    "units": "minutes",
    "timeOfDay": "morning",
    "summary": "Walk - 15 minutes - Lunchtime",
    "timeOfDayOrder": 1
  }
]

When I execute the following query:

select jsonb_array_elements(day.plan_activities) as activities 
from day 
where day.id = 18;

I get the following error:

Failed to run sql query: cannot extract elements from a scalar

The JSON contains a valid JSON array as far as I can tell. What am I doing wrong?

My eventual goal if I can extract this list is to create separate records elsewhere, each of which contains all the fields plus a reference back to the day record.

Are there any nulls? `select plan_activities from day where id = 18 and plan_activities is null` — Schwern, Feb 08 '23 at 06:52
Would you show us your table schema, please? `\d+ day` in psql. — Schwern, Feb 08 '23 at 06:53
Your JSON syntax is ok, and I tried your Json via using `jsonb_array_elements` this is works. May be `where day.id = 18` gets another record which doesn't have Json array. The function `jsonb_array_elements` gets error when your Json string has not Json arrays. — Ramin Faracov, Feb 08 '23 at 07:07
No nulls found running select plan_activities from day where id = 18 and plan_activities is null. id is a primary key in this case. — Schoxy, Feb 08 '23 at 07:07
( id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY, created_at timestamp with time zone DEFAULT 'now()', summary_plan text COLLATE pg_catalog."default", summary_day text COLLATE pg_catalog."default", date date, total_minutes smallint DEFAULT '0'::smallint, cardio_minutes smallint DEFAULT '0'::smallint, strength_minutes smallint DEFAULT '0'::smallint, day_name text COLLATE pg_catalog."default", plan_activities jsonb, plan_activities_text text COLLATE pg_catalog."default", CONSTRAINT day_pkey PRIMARY KEY (id) ) — Schoxy, Feb 08 '23 at 07:09
I deleted all records in the table except for the one with id = 18 and got the same error — Schoxy, Feb 08 '23 at 07:12
It's possible the whole data structure was accidentally added as a single JSON string. See the update to my answer. — Schwern, Feb 08 '23 at 19:41

Schwern · Answer 1 · 2023-02-08T19:43:02.033

This error happens when you try to treat a JSON scalar, like a single string or number, as an array.

-- ERROR:  cannot extract elements from a scalar
select jsonb_array_elements('23'::jsonb);

One of the rows of your query does not contain a JSON array.

Check with select plan_activities from day where id = 18. Although id is normally a unique primary key and it should be impossible to have more than one row returned.

Another way this could happen is if the JSON structure was accidentally added as a single JSON string.

-- 1, 2, 3
select jsonb_array_elements('[1, 2, 3]'::jsonb);


-- Note the extra quotes.
-- ERROR:  cannot extract elements from a scalar
select jsonb_array_elements('"[1, 2, 3]"'::jsonb);

Postgres jsonb_array_elements() returns "cannot extract elements from a scalar", even when given a valid JSON array?

1 Answers1