无法从标量中提取元素 [英] cannot extract elements from a scalar

问题描述

我有2个桌子的公司和联系方式.联系人具有地址JSONB列. 我在contacts.linked_to_company上使用join尝试了一个select语句，并使用了jsonb_array_elements(company.addresses)，但出现错误无法从标量中提取元素"，这是我理解的，因为某些条目的列地址中确实有[null].我已经看到了使用合并或CASE语句的答案.合并我无法工作，CASE示例在select语句中，如何在联接中使用它? 这是sql

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

这是示例jsonb

[{"PostCode":"BN7788","Address":"South Street","AddressFull":"","Types":[{"Type":"Collection"}]}]

推荐答案

您可以尝试以下方法之一(而不是jsonb_array_elements(t.addresses) address):

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) address
-- or
jsonb_array_elements(case jsonb_typeof(addresses) when 'array' then addresses else '[{"PostCode": null}]' end) address

第一个隐藏列的json格式不正确的行，第二个为它们提供null.

The first one hides rows with improper json format of the column, the second one gives null for them.

但是，问题实际上出在该列中的一个或多个值不是json数组.您可以使用以下命令轻松修复它:

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]'
where jsonb_typeof(addresses) <> 'array' or addresses = '[]';

此更正后，您将不需要jsonb_array_elements()中的case.

After this correction you won't need case in jsonb_array_elements().

这篇关于无法从标量中提取元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！