How can I select a JSON_VALUE if the object name contains a single quote?

Question

I am trying to get a value from some JSON in a table. This is my query:

Select JSON_VALUE(JSON_Data, '$.Person's_ID') FROM Table 

To try and get the value for Person's Id. This is (part of) the JSON stored in the JSON_Data column:

"Person's_ID": "Test"

I have tried using two single quotes but this still gives an error:

JSON path is not properly formatted. Unexpected character ''' is found at position 8."

Using a backslash to escape the single quote also does not work.

Answer 1

This might be a good lesson to not make garbage JSON (and validate it before inserting it into the database) since, according to the standard, single quote is not an accepted character in an entity name. :-)

You could try this type of replacement, but performance is going to be pretty craptastic, and it will replace all instances of Person's_ID, not just when it appears as an object:

SELECT JSON_VALUE(REPLACE(JSON_Data,'Person''s_ID','Person_s_ID'), '$.Person_s_ID')
  FROM dbo.Table;

Working example:

DECLARE @d varchar(255) = '{"Person''s_ID": "Test"}';
SELECT JSON_VALUE(REPLACE(@d,'Person''s','Person_s'), '$.Person_s_ID');

Result:

Test