CODEWITHSUNDEEP
mysqlsqljson
Jeni Biliyawala
Jeni Biliyawala

Reputation: 31

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

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).

Upvotes: 0

Views: 529

Answers (1)

Kiran A B
Kiran A B

Reputation: 931

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

Upvotes: 3

Related Questions