CODEWITHSUNDEEP
sqljsonpostgresqljsonbjsonb-array-elements
Schoxy
Schoxy

Reputation: 1

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

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.

Upvotes: 0

Views: 1794

Answers (1)

Schwern
Schwern

Reputation: 165436

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

Upvotes: 1

Related Questions