I want to extract an element from a json in a column.
However, the key of the element I am interested contains % (the exact name is: Use%).
Following this, I tried to use double quotes, however, I still have the same problem:
JSON_VALUE(results, '$."Use%"') as value
JSON text is not properly formatted. Unexpected character ”’ is found
at position 1.
JSON_VALUE(results, '$.Use%') as value
JSON path is not properly formatted. Unexpected character ‘%’ is found
at position 5.
How can I extract the value from my json string ?
The JSON is the following:
{'Filesystem': 'hdfs://nameservice1', 'Size': '67945349394432', 'Used': '22662944968704', 'Available': '41812184838144', 'Use%': '33%'}
>Solution :
The problem isn’t your attempt, it’s your JSON; it isn’t valid JSON. JSON uses double quotes (") for delimit identifing not single quotes ('). For the example we have, simply REPLACEing the single quotes with double quotes fixes the problem:
DECLARE @YourJSON nvarchar(MAX) = N'{''Filesystem'': ''hdfs://nameservice1'', ''Size'': ''67945349394432'', ''Used'': ''22662944968704'', ''Available'': ''41812184838144'', ''Use%'': ''33%''}';
SELECT JSON_VALUE(REPLACE(@YourJSON, '''', '"'), '$."Use%"') AS value;
Of course, I strongly suggest you investigate how you are creating JSON which uses single quotes, rather than double quotes, and fix both your existing data and process that creates it.