Databricks SQL查询静态表RAW JSON字符串仅返回NULL问题排查

问题背景 #

我创建了一张名为hl7_pat的静态表，表中存储了FHIR格式的JSON字符串，创建表的SQL语句如下：

CREATE TABLE hl7_pat AS SELECT
'{
    "resourceType": "Patient",
    "id": "f001",
    "text": {
        "status": "generated"
    },
    "identifier": [
        {
            "use": "usual",
            "system": "urn:oid:2.16.840.1.113883.2.4.6.3",
            "value": "738472983"
        },
        {
            "use": "usual",
            "system": "urn:oid:2.16.840.1.113883.2.4.6.3"
        }
    ],
    "active": true,
    "name": [
        {
            "use": "usual",
            "family": "van de Heuvel",
            "given": [
                "Pieter"
            ],
            "suffix": [
                "MSc"
            ]
        }
    ],
    "telecom": [
        {
            "system": "phone",
            "value": "0648352638",
            "use": "mobile"
        },
        {
            "system": "email",
            "value": "p.heuvel@gmail.com",
            "use": "home"
        }
    ],
    "gender": "male",
    "birthDate": "1944-11-17",
    "deceasedBoolean": false,
    "address": [
        {
            "use": "home",
            "line": [
                "Van Egmondkade 23"
            ],
            "city": "Amsterdam",
            "postalCode": "1024 RJ",
            "country": "NLD"
        }
    ],
    "maritalStatus": {
        "coding": [
            {
                "system": "http://terminology.hl7.org/CodeSystem/v3-MaritalStatus",
                "code": "M",
                "display": "Married"
            }
        ],
        "text": "Getrouwd"
    },
    "multipleBirthBoolean": true,
    "contact": [
        {
            "relationship": [
                {
                    "coding": [
                        {
                            "system": "http://terminology.hl7.org/CodeSystem/v2-0131",
                            "code": "C"
                        }
                    ]
                }
            ],
            "name": {
                "use": "usual",
                "family": "Abels",
                "given": [
                    "Sarah"
                ]
            },
            "telecom": [
                {
                    "system": "phone",
                    "value": "0690383372",
                    "use": "mobile"
                }
            ]
        }
    ],
    "communication": [
        {
            "language": {
                "coding": [
                    {
                        "system": "urn:ietf:bcp:47",
                        "code": "nl",
                        "display": "Dutch"
                    }
                ],
                "text": "Nederlands"
            },
            "preferred": true
        }
    ],
    "managingOrganization": {
        "reference": "Organization/f001",
        "display": "Burgers University Medical Centre"
    }
}
' as raw;

尝试用SELECT raw:resourceType FROM hl7_pat;查询JSON中的resourceType字段，预期返回"Patient"，但实际返回NULL。

原因分析 #

核心问题是：创建表时，raw字段被默认定义为普通字符串类型，而非JSON类型。数据库无法直接通过:这种JSON路径语法去解析普通字符串，因此返回NULL。

解决方法 #

不同数据库的JSON处理语法不同，以下是主流数据库的修正方案：

MySQL/MariaDB

• 临时修正查询语句：

-- 使用JSON_EXTRACT函数
SELECT JSON_EXTRACT(raw, '$.resourceType') FROM hl7_pat;
-- 或者使用简化箭头语法
SELECT raw->>'$.resourceType' FROM hl7_pat;

• 从根源解决，重新创建表时指定JSON类型：

CREATE TABLE hl7_pat (raw JSON);
INSERT INTO hl7_pat VALUES ('{/* 原JSON字符串 */}');

PostgreSQL

• 临时修正查询语句：

SELECT raw::json->>'resourceType' FROM hl7_pat;

• 从根源解决，使用jsonb类型存储（推荐）：

CREATE TABLE hl7_pat (raw jsonb);
INSERT INTO hl7_pat VALUES ('{/* 原JSON字符串 */}');

SQL Server

• 使用JSON_VALUE函数查询：

SELECT JSON_VALUE(raw, '$.resourceType') FROM hl7_pat;

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