如何在PySpark SQL中将结构体类型列拆分为单独字符串列？

你的filters列是嵌套JSON结构：外层JSON包含value字段，而value本身是转义后的JSON字符串。要拆分出多列，需要分两步处理：先提取外层的value内容，再解析这个子JSON字符串。

针对Hive（你提到的JSON_TUPLE适用场景） #

Hive中可以通过嵌套使用json_tuple来实现：

SELECT
  -- 拆分value中的各个字段为单独列
  json_tuple(t.value_str, 'shipment', 'load', 'purchase', 'department', 'vendor', 'tmLoc', 'inYardGoalDate') 
  AS (shipment, load, purchase, department, vendor, tmLoc, inYardGoalDate)
FROM (
  -- 先从外层JSON提取出value字段的原始JSON字符串
  SELECT json_tuple(filters, 'value') AS value_str
  FROM your_table
) t;

为什么之前用JSON_TUPLE返回[]？ #

你直接对整个filters列提取shipment是错误的——shipment是value子JSON里的键，不是外层JSON的键，外层只有key、location、value三个键，所以直接提取会返回空数组。

针对MySQL #

如果使用MySQL，可通过JSON_UNQUOTE和JSON_EXTRACT组合解析，或用JSON_TABLE（MySQL 8.0+支持）：

方法1：嵌套解析 #

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.shipment')) AS shipment,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.load')) AS load,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.purchase')) AS purchase,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.department')) AS department,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.vendor')) AS vendor,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.tmLoc')) AS tmLoc,
  JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(filters->>'$.value'), '$.inYardGoalDate')) AS inYardGoalDate
FROM your_table;

方法2：使用JSON_TABLE（更简洁） #

SELECT
  j.shipment,
  j.load,
  j.purchase,
  j.department,
  j.vendor,
  j.tmLoc,
  j.inYardGoalDate
FROM your_table t,
JSON_TABLE(
  JSON_UNQUOTE(t.filters->>'$.value'),
  '$' COLUMNS(
    shipment JSON PATH '$.shipment',
    load JSON PATH '$.load',
    purchase JSON PATH '$.purchase',
    department JSON PATH '$.department',
    vendor JSON PATH '$.vendor',
    tmLoc JSON PATH '$.tmLoc',
    inYardGoalDate VARCHAR(20) PATH '$.inYardGoalDate'
  )
) j;

针对PostgreSQL #

PostgreSQL中可通过jsonb类型转换来处理：

SELECT
  (value_json ->> 'shipment') AS shipment,
  (value_json ->> 'load') AS load,
  (value_json ->> 'purchase') AS purchase,
  (value_json ->> 'department') AS department,
  (value_json ->> 'vendor') AS vendor,
  (value_json ->> 'tmLoc') AS tmLoc,
  (value_json ->> 'inYardGoalDate') AS inYardGoalDate
FROM (
  SELECT (filters::jsonb ->> 'value')::jsonb AS value_json
  FROM your_table
) t;

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