时间戳

首页 > 解决方案 > 错误：无法在非数组上调用 json_array_elements

sql - 错误：无法在非数组上调用 json_array_elements

问题描述

我有一个 Postgres 查询，它返回以下值。此查询返回 id 的一个数据。

select h ->> 'dw:address_line_1' as address, h ->> 'dw:city' as city, h -> 'dw:region' -> 'dw:ID' -> 2 -> '#text' as state, h ->'dw:Country_ISO_Code' as country
from testschema.dw_data_job t, json_array_elements(t.addresses::json) h
where t.id = '12345'
and h -> 'dw:address_type' -> 'dw:ID' -> 1 ->> '#text' = 'BUSINESS'

现在我想在表格中获取整个人口的商业地址dw_data_job。我将查询更改为如下。

select h ->> 'dw:address_line_1' as address, h ->> 'dw:city' as city, h -> 'dw:region' -> 'dw:ID' -> 2 -> '#text' as state, h ->'dw:Country_ISO_Code' as country
from testschema.dw_data_job t, testschema.dw_data_demo t1, json_array_elements(t.addresses::json) h
where t.id = t1.id
and h -> 'dw:address_type' -> 'dw:ID' -> 1 ->> '#text' = 'BUSINESS'

现在我得到如下错误：

ERROR:  cannot call json_array_elements on a non-array
CONTEXT:  parallel worker
SQL state: 22023

列的类型dw_data_job.addresses和text数据如下所示 - 当 JSON 值为数组时：

[{"dw:address_line_1": "123 Inter Pkwy", "dw:city": "Richardson", "dw:region": {"@dw:Descriptor": "Texas", "dw:ID": [{"@dw:type": "WID", "#text": "fc77e3a1ab36487f9646d14f7242dd77"}, {"@dw:type": "Country_Region_ID", "#text": "USA-TX"}, {"@dw:type": "ISO_3166-2_Code", "#text": "TX"}]}, "dw:region_subdivision_1": "Dallas", "dw:postal_code": "75081", "dw:Country_ISO_Code": "USA", "dw:address_type": {"@dw:Descriptor": "Business", "dw:ID": [{"@dw:type": "WID", "#text": "4fae289a7fe541b098ca9448e462ff6b"}, {"@dw:type": "Communication_Usage_Type_ID", "#text": "BUSINESS"}]}, "dw:primary": "1"}, {"dw:address_line_1": "567 South Dr", "dw:city": "Plano", "dw:region": {"@dw:Descriptor": "Texas", "dw:ID": [{"@dw:type": "WID", "#text": "fc77e3a1ab36487f9646d14f7242dd77"}, {"@dw:type": "Country_Region_ID", "#text": "USA-TX"}, {"@dw:type": "ISO_3166-2_Code", "#text": "TX"}]}, "dw:region_subdivision_1": "Collin", "dw:postal_code": "75024", "dw:Country_ISO_Code": "USA", "dw:address_type": {"@dw:Descriptor": "Home", "dw:ID": [{"@dw:type": "WID", "#text": "836cf00ef5974ac08b786079866c946f"}, {"@dw:type": "Communication_Usage_Type_ID", "#text": "HOME"}]}, "dw:primary": "1"}, {"dw:address_line_1": "789 North Dr.", "dw:city": "Plano", "dw:region": {"@dw:Descriptor": "Texas", "dw:ID": [{"@dw:type": "WID", "#text": "fc77e3a1ab36487f9646d14f7242dd77"}, {"@dw:type": "Country_Region_ID", "#text": "USA-TX"}, {"@dw:type": "ISO_3166-2_Code", "#text": "TX"}]}, "dw:region_subdivision_1": "Collin", "dw:postal_code": "75024", "dw:Country_ISO_Code": "USA", "dw:address_type": {"@dw:Descriptor": "Home-Vac", "dw:ID": [{"@dw:type": "WID", "#text": "836cf00ef5974ac08b786079866c946f"}, {"@dw:type": "Communication_Usage_Type_ID", "#text": "HOME-VAC"}]}, "dw:communication_usage_behavior": {"@dw:Descriptor": "Mailing", "dw:ID": [{"@dw:type": "WID", "#text": "bea4505497c901ea53792e2628077617"}, {"@dw:type": "Communication_Usage_Behavior_Tenanted_ID", "#text": "MAILING"}]}, "dw:primary": "0"}]

另一个带有非数组（对象）的样本值：

{"dw:address_line_1": "123 Local Pkwy", "dw:city": "Cary", "dw:region": {"@dw:Descriptor": "North Carolina", "dw:ID": [{"@dw:type": "WID", "#text": "1486a0a4a8464c3b9ec482d4038deb99"}, {"@dw:type": "Country_Region_ID", "#text": "USA-NC"}, {"@dw:type": "ISO_3166-2_Code", "#text": "NC"}]}, "dw:region_subdivision_1": "Wake", "dw:postal_code": "27513", "dw:Country_ISO_Code": "USA", "dw:address_type": {"@dw:Descriptor": "Business", "dw:ID": [{"@dw:type": "WID", "#text": "4fae289a7fe541b098ca9448e462ff6b"}, {"@dw:type": "Communication_Usage_Type_ID", "#text": "BUSINESS"}]}, "dw:primary": "1"}

查询需要同时容纳数组和非数组 json 数据，以便为嵌套键#text具有 value的所有数据行返回地址、城市、州和国家/地区代码'BUSINESS'。

标签： sqlarraysjsonpostgresql

解决方案

就像错误消息告诉您（并且 Jeff 指出）在 table 中有一行或多行dw_data_job，其中列addresses不包含有效的 JSON 数组（或NULL）。一个有效的 JSON 文字，是的，或者我们会看到由失败的强制转换引发的不同错误消息::json，但不是 JSON数组。

运行此查询以识别违规行：

SELECT id, addresses
     , jsonb_pretty(addresses::jsonb) AS js_pretty  -- optional
FROM   dw_data_job
WHERE  json_typeof(addresses::json) IS DISTINCT FROM 'array';

（包括 NULL 值addresses，这不会引发报告的错误，但也可能是一个问题。）

要跳过包含无效数据的行并继续查询：

SELECT h ->> 'dw:address_line_1' AS address
     , h ->> 'dw:city' AS city
     , h #>> '{dw:region,dw:ID,2,#text}' AS state
     , h ->> 'dw:Country_ISO_Code' AS country
     , json_typeof(t.addresses::json)
FROM   testschema.dw_data_job t
JOIN   testschema.dw_data_demo t1 USING (id)
CROSS  JOIN json_array_elements(t.addresses::json) h
WHERE  json_typeof(t.addresses::json) = 'array'
AND    h #>> '{dw:address_type,dw:ID,1,#text}' = 'BUSINESS';

我使用紧凑运算符简化了#>深层嵌套值的语法，并返回全部text而不是一些json（作为有根据的猜测）。

要包含普通对象和数组，您可以：

WITH sel AS (  -- compute json & type
   SELECT t.addresses::json AS a, json_typeof(t.addresses::json) AS js_type
   FROM   testschema.dw_data_job t
   JOIN   testschema.dw_data_demo t1 USING (id)
   )
 , obj AS (  -- unnest arrays and union with plain objects
   SELECT json_array_elements(a) AS h FROM sel WHERE js_type = 'array'
   UNION ALL
   SELECT a                           FROM sel WHERE js_type = 'object'
   -- all other types are ignored!
   )
SELECT h ->> 'dw:address_line_1' AS address
     , h ->> 'dw:city' AS city
     , h #>> '{dw:region,dw:ID,2,#text}' AS state
     , h ->> 'dw:Country_ISO_Code' AS country
FROM   obj
WHERE  h #>> '{dw:address_type,dw:ID,1,#text}' = 'BUSINESS';

db<>在这里摆弄

推荐阅读