Get value from JSON object having special character in key using SQL query-CodePudding

I have an outlet_details table having two columns(id and extended_attributes as a JSON object). extended_attributes have values like

{
    "parent-0-0-id": "DS-606",
    "parent-0-1-id": "SD066",
    "secondaryOutletCode": "MG_918"
}

I want to get parent-0-0-id's value, but when I'm trying to hit

SELECT extended_attributes->>'$.parent-0-0-id' AS 'parent00id' FROM outlet_details;

I'm getting an:

invalid JSON path expression error(3143).

CodePudding user response：

One method is OPENJSON:

DECLARE @JSON nvarchar(MAX) = N'{
    "parent-0-0-id": "DS-606",
    "parent-0-1-id": "SD066",
    "secondaryOutletCode": "MG_918"
}';

SELECT OJ.[parent-0-1-id]
FROM (VALUES(@Json))YT(YourJsonColumn)
     CROSS APPLY OPENJSON(YT.YourJsonColumn)
                 WITH ([parent-0-1-id] varchar(30)) OJ;

CodePudding user response：

You could just enclose the column name under quotes to separate out the name from escape characters.

SELECT extended_attributes->>"$.\"parent-0-0-id\"" AS 'parent00id' FROM outlet_details; should work