Databricks SQL: only NULL value is returning when querying a RAW "JSON" string from static table

Question

I am trying to pull out data elements from the following JSON file//custom table that I created. Data is anonymous from the HIE FHIR website (src: https://www.hl7.org/fhir/patient-example-f001-pieter.json.html)

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": "[email protected]",
            "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;

I am using the following syntax to try to get data out of it.

SELECT raw:resourceType from hl7_pat;

The above query should simply return "Patient" however it returns NULL. Any suggestions on why this isn't working?

I tried to run: SELECT raw:resourceType FROM hl7_pat; but got NULL value. I was expecting to see "Patient"

EDIT1: adding a screenshot of just running "SELECT * FROM.." enter image description here

Answer 1

It is working as expected.

You can also use from_json function to extract property from json object.