JSON text is not properly formatted. Unexpected character ''' is found at position 1

Question

Question: Why am I unable to apply the OPENJSON function to read the data below into standard columnar form? How can I modify the query or the JSON text to solve this problem?

Context: I have some JSON data in a global temp table in SQL Server that I am trying to parse and load into standard table.

SELECT * FROM ##json_loop_tracking;

Returns the single row data in the following structure:

{'sends': 0, 'opens': 0, 'clicks': 0, 'forwards': 0, 'unsubscribes': 0, 'bounces': 0, 'spam_count': 0}

When trying to apply the OPENJSON function in code block below I get the following error:

Msg 13609, Level 16, State 4, Line 51 JSON text is not properly formatted. Unexpected character ''' is found at position 1.

INSERT INTO ##jsonparsed_tracking (
sends,opens,clicks,forwards,unsubscribes,bounces,spam_count
)
SELECT DISTINCT
jsn.sends, jsn.opens, jsn.clicks, jsn.forwards, jsn.unsubscribes, jsn.bounces,jsn.spam_count
FROM ##json_loop_tracking
OUTER APPLY (

SELECT * FROM OPENJSON(##json_loop_tracking.my_json)
WITH (
    sends int '$.sends',
    opens int '$.opens',
    clicks int '$.clicks',
    forwards int '$.forwards',
    unsubscribes int '$.unsubscribes',
    bounces int '$.bounces',
    spam_count int '$.spam_count'
)

) AS jsn

Answer 1

Single quoted, like your data is, isn't proper JSON. However you can still use it if you replace with double-quotes (if all your data is like this):

>>> a = "{'sends': 0, 'opens': 0, 'clicks': 0, 'forwards': 0, 'unsubscribes': 0, 'bounces': 0, 'spam_count': 0}"

>>> import json
>>> json.loads(a)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Program Files\Python37\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Program Files\Python37\lib\json\decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "C:\Program Files\Python37\lib\json\decoder.py", line 353, in raw_decode
    obj, end = self.scan_once(s, idx)
json.decoder.JSONDecodeError: Expecting property name enclosed in double quotes: line 1 column 2 (char 1)

It's a different module and a different phrasing but basically the same error. We fix it like this:

>>> json.loads(a.replace("'", '"'))
{'sends': 0, 'opens': 0, 'clicks': 0, 'forwards': 0, 'unsubscribes': 0, 'bounces': 0, 'spam_count': 0}

Notice I used a.replace("'", '"') to fix the problem, this might fail if your data contains more complicated objects