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%'}

CodePudding user response：

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.