Extract value from mysql json string stored as varchar-CodePudding

Have a mysql column with json strings stored as varchar:

{'@type': 'Organization', 'legalName': 'some company inc.'}

I tried to extract it using the following:

SELECT JSON_EXTRACT(columnname, "$.legalName")
FROM tablename
WHERE indexfield='specifics'

But all i get is 'NULL' as output and a warning message " Current selection does not contain a unique column."

CodePudding user response：

JSON has to have its names and values wrapped in DOUBLE QUOTES like this

{"@type": "Organization", "legalName": "some company inc."}

SELECT JSON_EXTRACT('{"@type": "Organization", "legalName": "some company inc."}', "$.legalName") as xxx;

Works

"some company inc."

While your example has single quotes

{'@type': 'Organization', 'legalName': 'some company inc.'}

SELECT JSON_EXTRACT("{'@type': 'Organization', 'legalName': 'some company inc.'}", "$.legalName") as xxx;

Does not work!

So it looks like the JSON you stored was not real JSON