PostgreSQL提取orders表addressJson列JSON数据报错求助

问题描述 #

我有一张名为orders的表，其中包含addressJson列，存储JSON格式数据，需要编写PostgreSQL查询提取所有行的JSON数据，用于在Tableau生成新表。

尝试的SQL语句：

SELECT "orders"."id", "orders"."order_id", "addressJson"."full_name"
FROM "public"."orders" "orders"
CROSS JOIN LATERAL (
SELECT (addressJson->>'status') AS status, (addressJson->>'full_name') AS full_name
FROM jsonb_array_elements(("orders.addressJson")) AS addressJson
) AS addressJson

执行时报错：

ERROR:  column "orders.addressJson" does not exist
LINE 5: FROM jsonb_array_elements(("orders.addressJson")) AS address...
                                   ^ 

SQL state: 42703

JSON数据示例：

{
  "full_name": "xxx",
  "contact_number": "xxxx",
  "title": "xxxx",
  "zone_number": "0",
  "street_number": "1",
  "building_number": "27",
  "business_address": "xxx",
  "latitude": 123,
  "longitude": 123,
  "country": "xxx",
  "apartment_number": "27"
}

解决方案 #

问题根源 #

1. 列引用格式错误：orders.addressJson的写法不符合PostgreSQL规则，带引号的列名必须用双引号单独包裹，正确写法是"orders"."addressJson"
2. 函数使用场景错误：你的JSON示例是单个JSON对象，并非JSON数组，但你用了jsonb_array_elements（专门用于解析JSON数组的函数），这会导致解析失败。

修正后的SQL #

如果addressJson列始终存储单个JSON对象，直接提取字段即可，无需关联数组解析函数：

SELECT 
  "orders"."id", 
  "orders"."order_id",
  "orders"."addressJson"->>'full_name' AS full_name,
  "orders"."addressJson"->>'contact_number' AS contact_number,
  "orders"."addressJson"->>'title' AS title,
  "orders"."addressJson"->>'zone_number' AS zone_number,
  "orders"."addressJson"->>'street_number' AS street_number,
  "orders"."addressJson"->>'building_number' AS building_number,
  "orders"."addressJson"->>'business_address' AS business_address,
  ("orders"."addressJson"->>'latitude')::numeric AS latitude,
  ("orders"."addressJson"->>'longitude')::numeric AS longitude,
  "orders"."addressJson"->>'country' AS country,
  "orders"."addressJson"->>'apartment_number' AS apartment_number
FROM "public"."orders" "orders";

如果后续addressJson可能存储JSON数组，需要处理数组场景时，再使用CROSS JOIN LATERAL，注意列引用格式：

SELECT 
  "orders"."id", 
  "orders"."order_id",
  addr->>'full_name' AS full_name,
  addr->>'contact_number' AS contact_number
FROM "public"."orders" "orders"
CROSS JOIN LATERAL jsonb_array_elements("orders"."addressJson") AS addr;

内容的提问来源于stack exchange，提问作者Saamia