cannot extract elements from a scalar
You can try one of these (instead of jsonb_array_elements(t.addresses) address
):
jsonb_array_elements(
case jsonb_typeof(addresses)
when 'array' then addresses
else '[]' end
) as address
-- or
jsonb_array_elements(
case jsonb_typeof(addresses)
when 'array' then addresses
else '[{"PostCode": null}]' end
) as address
The first one hides rows with improper json format of the column, the second one gives null
for them.
However, the problem actually stems from that one or more values in the column is not a json array. You can easily fix it with the command:
update contact
set addresses = '[null]'
-- or
-- set addresses = '[{"PostCode": null}]'
where jsonb_typeof(addresses) <> 'array' or addresses = '[]';
After this correction you won't need case
in jsonb_array_elements()
.
MoishAsh
Updated on June 17, 2022Comments
-
MoishAsh almost 2 years
I have 2 tables company and contacts. Contacts has addresses JSONB column. I tried a select statement with a join on contacts.linked_to_company and using jsonb_array_elements(company.addresses) but I get error 'cannot extract elements from a scalar' which I understand is because some entries do have [null] in column address. I have seen answers to use coalesce or a CASE statement. Coalesce I could get to not work and CASE example is in the select statement how do use it in a join? Here is the sql
SELECT company.id, trading_name, nature_of_business, t.id contactID, address->>'PostCode' Postcode, position_in_company FROM contact t FULL JOIN company ON (t.company_linked_to = company.id ), jsonb_array_elements(t.addresses) address WHERE t.company_linked_to ='407381';
here is example jsonb
[{"PostCode":"BN7788","Address":"South Street","AddressFull":"","Types":[{"Type":"Collection"}]}]
-
MoishAsh almost 6 yearsThanks for the answer, if i would like to retrieve all information from jsonb i.e. Address, AddressFull, would every field in jsonb have to be nulled in ELSE statement were there is no valid 'array'?
-
klin almost 6 yearsData correction is a better solution, see the updated answer.
-
Soren about 5 yearsThe answer I was looking for! Helping to resolve the case issue and SRFs caused by Postgresql 10 update. Move the case inside the jsonb_array_elements instead outside it. Also a reference to a blog for other passersby: tapoueh.org/blog/2017/10/…