如何使用EXTRACTJSONFIELD过滤ksql中的嵌套JSON数据

PAYLOAD列存储经JSON字符串化处理的值，存储示例如下：

"{\"key\":{\"subkey\":\"subvalue\"}}"

执行以下KSQL语句时系统返回错误，需要调整过滤逻辑，实现仅当subkey字段值等于subvalue时返回匹配结果：

SELECT EXTRACTJSONFIELD(PAYLOAD, '$.key') AS key FROM my_stream WHERE (key => subkey = 'subvalue');

原语句报错核心有两点：

• EXTRACTJSONFIELD提取出的key是字符串类型的JSON片段，不是可直接通过=>操作符访问子字段的结构化JSON对象，直接做结构访问会触发类型错误。
• ksqlDB的WHERE子句执行优先级高于SELECT阶段的别名计算，无法直接在WHERE里引用SELECT里定义的key别名做字段访问，会触发字段不存在的语法错误。
  另外示例中的PAYLOAD是双重序列化的字符串（外层额外包了一层引号），直接按路径提取也会因为外层是纯字符串拿不到内部字段。

方案1：直接嵌套提取目标过滤字段（适配单次查询场景） #

不需要单独提取key再访问子字段，直接在WHERE子句里通过JSON路径提取目标subkey字段做值判断即可。针对示例里的双重序列化存储，先取根节点解掉外层字符串包装，再提取目标字段：

SELECT EXTRACTJSONFIELD(EXTRACTJSONFIELD(PAYLOAD, '$'), '$.key') AS key 
FROM my_stream 
WHERE EXTRACTJSONFIELD(EXTRACTJSONFIELD(PAYLOAD, '$'), '$.key.subkey') = 'subvalue';

如果PAYLOAD本身是标准JSON（示例里的转义仅为显示效果，没有外层额外的字符串包裹），可以去掉一层提取逻辑：

SELECT EXTRACTJSONFIELD(PAYLOAD, '$.key') AS key 
FROM my_stream 
WHERE EXTRACTJSONFIELD(PAYLOAD, '$.key.subkey') = 'subvalue';

方案2：提前结构化解析（适配频繁查询场景，性能更好） #

如果需要频繁访问PAYLOAD内的字段，可以在建流阶段就把JSON字段解析成结构化列，后续查询直接过滤普通列即可，不需要每次运行时做JSON提取：

-- 先创建解析后的结构化流
CREATE STREAM my_stream_structured AS
SELECT
  EXTRACTJSONFIELD(PAYLOAD, '$.key') AS key,
  EXTRACTJSONFIELD(PAYLOAD, '$.key.subkey') AS subkey
FROM my_stream;

-- 后续查询直接过滤结构化列
SELECT key FROM my_stream_structured WHERE subkey = 'subvalue';

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