Can't parse JSONB scalar as array in Postgres

Question

I'm on PostgreSQL 13.2. I have a table with a JSONB column, and it stores JSONs that are a list of objects:

"[{\"MyKey\":\"ValueXYZ\",\"Counter\":0}, {\"MyKey\":\"ValueABC\",\"Counter\":3}]"

When I test this column for a type with jsonb_typeof() like so:

select jsonb_typeof(i.my_column) as col_type
from items i 
where i.id = 342

I get string. Which tells me this value is a scalar, and I'm wondering if maybe it wasn't inserted properly.

The error that is bothering me is I am trying to parse the column with something like this:

select jsonb_array_elements(i.my_column)
from items i

and I see the error:

SQL Error [22023]: ERROR: cannot extract elements from a scalar

What is going on? Is there a way to fix this?

jjanes · Accepted Answer · 2021-04-13 19:04:23Z

1

Yes, it got inserted wrong. It contains a scalar, which happens to be holding the string representation of a JSON array. The string representation of a JSON array is a different thing than an actual JSON array.

You can extract the string value from the scalar, then cast that string into a jsonb. #>>'{}' will extract the string out of a scalar.

select jsonb_array_elements((i.my_column#>>'{}')::jsonb)
from items i ;

Although you should fundamentally fix the problem, by re-storing the values correctly.

update items set my_column = (my_column#>>'{}')::jsonb where jsonb_typeof(my_column)='string';

But of course you should fix whatever is doing the incorrect insertions first.

answered Apr 13, 2021 at 19:04

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2021-04-13 17:49:33Z

0

Looks like a quoting issue. Consider:

test=> SELECT jsonb_typeof(jsonb '[{"MyKey":"ValueXYZ","Counter":0}, {"MyKey":"ValueABC","Counter":3}]') AS col_type;
 col_type 
----------
 array
(1 row)

answered Apr 13, 2021 at 17:49

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Can't parse JSONB scalar as array in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related