BigQuery JSON函数解析格式不规范字符串无法提取全部值问题

问题结论 #

你的判断完全正确，BigQuery的JSON解析逻辑会在遇到第一个不符合标准JSON规范的语法点时终止解析，仅返回错误点之前可匹配的路径值，错误点之后的所有内容都会返回null。

根因分析 #

你存储的字符串并非符合RFC 8259标准的JSON格式，而是Python字典对象序列化后的字符串表示，存在3处语法不兼容问题：

• 字符串边界使用单引号，标准JSON要求必须使用双引号
• 空值使用Python风格的None，标准JSON要求使用小写null
• 布尔值使用Python风格的True/False，标准JSON要求使用小写true/false

你示例中第一个语法错误出现在ip_address: None的位置，错误点之前的language、url_variables等结构符合规范，可以正常解析；错误点之后的response_time、survey_data等内容都会被解析器忽略，因此返回null。

不同工具表现差异原因 #

• Python可以正常解析是因为你使用的是Python原生的字典反序列化逻辑（如ast.literal_eval），本身就是为解析Python字典字符串设计的，并非标准JSON解析
• jq严格遵循标准JSON规范，遇到语法错误直接抛出异常，不会做部分兼容解析

解决方案 #

在BigQuery中解析前先做字符串清洗，将非标准格式替换为标准JSON格式即可正常提取所有字段，示例代码如下：

DECLARE resp STRING 
DEFAULT "{'id': '111', 'contact_id': '', 'status': 'Complete', 'is_test_data': '0', 'date_submitted': '2021-07-08 17: 02: 16 GMT', 'session_id': '111', 'language': 'Eng', 'date_started': '2021-02-08 16: 56: 55 GMT', 'link_id': '111', 'url_variables': {'touchpoint': {'key': 'touchpoint', 'value': 'phone', 'type': 'url' }, 'interaction_id': {'key': 'interaction_id', 'value': '111', 'type': 'url' } }, 'ip_address': None, 'referer': '', 'user_agent': None, 'response_time': 111, 'data_quality': [], 'longitude': '', 'latitude': '', 'country': '', 'city': '', 'region': '', 'postal': '', 'dma': '', 'survey_data': {'25': {'id': 25, 'type': 'TEXTBOX', 'question': 'feedback_source', 'section_id': 1, 'shown': False }, '229': {'id': 229, 'type': 'TEXTBOX', 'question': 'recruitment_method', 'section_id': 1, 'shown': False }, '227': {'id': 227, 'type': 'TEXTBOX', 'question': 'meeting_point', 'section_id': 1, 'answer': 'phone', 'shown': True }, '221': {'id': 221, 'type': 'TEXTBOX', 'question': 'interaction_id', 'section_id': 1, 'answer': '222', 'shown': True }, '217': {'id': 217, 'type': 'TEXTBOX', 'question': 'session_id', 'section_id': 1, 'answer': '333', 'shown': True }, '231': {'id': 231, 'type': 'ESSAY', 'question': 'BlaBla question 4', 'section_id': 3, 'answer': 'Bla Bla answer', 'shown': True }, '255': {'id': 255, 'type': 'TEXTBOX', 'question': 'tz_offset', 'section_id': 3, 'answer': '-120', 'shown': True }, '77': {'id': 77, 'type': 'parent', 'question': 'Bla Bla 1', 'section_id': 35, 'options': {'10395': {'id': 10395, 'option': 'Neutraal', 'answer': '3' } }, 'shown': True }, '250': {'id': 250, 'type': 'RADIO', 'question': 'Bla Bla?', 'section_id': 66, 'original_answer': '1', 'answer': '1', 'answer_id': 10860, 'shown': True }, '251': {'id': 251, 'type': 'RADIO', 'question': 'Bla Bla', 'section_id': 66, 'original_answer': '0', 'answer': '0', 'answer_id': 10863, 'shown': True } } }";

WITH cleaned_json AS (
  SELECT 
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        REGEXP_REPLACE(
          REGEXP_REPLACE(resp, "'", '"'),
          r'\bNone\b', 'null'
        ),
        r'\bTrue\b', 'true'
      ),
      r'\bFalse\b', 'false'
    ) AS standard_resp
)

SELECT
  JSON_VALUE(standard_resp, '$.url_variables.interaction_id.value') as url_interaction_id_value,
  JSON_VALUE(standard_resp, '$.url_variables.interaction_id.type') as url_interaction_id_type,
  JSON_VALUE(standard_resp, '$.language') as language,
  JSON_VALUE(standard_resp, '$.response_time') as response_time,
  JSON_QUERY(standard_resp, '$.survey_data') as survey_data
FROM cleaned_json

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