How can I get value from json whose key has dot?

[boson234]

I have a json string，it looks like json '{"id.a": 1, "value": "xxx"}' ，
I tried to get the value of "id.a", and use "json_extract_scalar"
SELECT json_extract_scalar(json '{"id.a": 1, "value": "xxx"}', '$.id.a');
but the result is null.
I should treat "id.a" as whole,but I don't know how

[yuokada]

Maybe, you have to escape like below.

SELECT json_extract_scalar(json '{"id.a": 1, "value": "xxx"}', '$."id.a"');

I don't confirm the above query actually works. But, it will work.

[boson234]

I tried this, but failed.
and the version is 435

[hashhar]

Use square brackets. Like so: $["id.a"]

[hashhar]

You have to escape special characters in JSON path expressions using square brackets and quotes:

SELECT json_extract_scalar(json '{"id.a": 1, "value": "xxx"}', '$["id.a"]');

[boson234]

it doesn't work too.

[hashhar]

sorry, forgot to remove the .. Updated the answer.

. is not needed in the bracket notation.

[boson234]

yes，it works。thank you！