How to escape fields with "$" in the name

Question

How do I query a json document with field names like "$id"?

SELECT [ID]
FROM OPENJSON( '[{"$id":42},{"$id":43}]', '$' ) WITH ([ID] NVARCHAR(25) '$.$id')
WHERE ID = 42

Answer 1

You need to use quotes ('$."$id"'), if the key name in the path declaration starts with a dollar sign or contains special characters:

SELECT *
FROM OPENJSON('[{"$id":42}, {"$id":43}]', '$') 
   WITH ([ID] NVARCHAR(25) '$."$id"')
WHERE ID = 42