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.

• Schwern: Are there any nulls? select plan_activities from day where id = 18 and plan_activities is null
• Schwern: Would you show us your table schema, please? \d+ day in psql.
• Ramin Faracov: 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.
• Schoxy: 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: ( 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) )

Answer 1

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);