Search code examples
sqljsonsqlitejson-queryjson-extract

malformed JSON Error in Sqlite when i use json_extract funciton

In my sqllite table i have field named config which stores configuration in the form of json text..

bellow is the sample data stored in config field

{
"matRid": 1,
"holderType": 1,
"uomRid": 1,
"type": 502,
"stockConditionIndex": 800,
"serialRequired": 1,
"codepart": 1,
"allowEdit": 1
}

from this data when I run the bellow query it throws malformed JSON error

 select json_extract(config, '$.codepart') as codepart FROM TableName

to test whether json is in the correct format or not i have run the following query

select json_valid(config) from TableName

above query retuns 1, this confirms that json is in the proper format

and also if I run the query by directly like bellow, using the json value of the field config query will return result successfully,

select json_extract('{
"matRid": 1,
"holderType": 1,
"uomRid": 1,
"type": 502,
"stockConditionIndex": 800,
"serialRequired": 1,
"codepart": 1,
"allowEdit": 1
}', '$.codepart') as codepart FROM TableName

How to make json_extract work by using the column name instead of json value direclty in SqlLite?

additional info : SQLite Version 3.35.5.

any suggestions would be helpfull.. Thank you..

Solution

  • The error was actually produced by the json_extract for the rows with empty record (empty string) for the field config

    resolved it by including the IIF statement

    SELECT json_extract(json(IIF(config <> '', config, NULL)), '$.codepart') from TableName