Querying JSON Object in BIg Query-CodePudding

I have a json object in a column that looks like this

{
 "user.id": "635bdb70dc89a6e30cde1193",
 "user.firstname":"John"
}

I will like to destructure it and select only the firstname, I have tried

SELECT 
JSON_VALUE(user_properties, '$.user.firstname') AS firstname,

but no luck

CodePudding user response：

You can quote it

Select JSON_VALUE(user_properties,'$."user.firstname"')

Or you can use a CROSS APPLY and OPENJSON

SELECT j.* 
FROM YourTable A
CROSS APPLY OPENJSON(user_properties)
            WITH 
            (
                [user.firstname] VARCHAR(50)
            ) j;