1

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..

Mahesh Gouda
  • 118
  • 9
  • 1
    But it worked at sqlffidle https://www.db-fiddle.com/f/dCsKGkz7zzEQ3E3NEqbTfg/0 could you check your json data from your data base? – D-Shih May 05 '22 at 08:28
  • @D-Shih in the Table some of the field's for column `config` had `NULL` or `EMPTY` data; the Error was Produced by `json_extract` for the Row with `EMPTY` data (empty string).. resolved it By including a `IIF statement` to check empty field in query.. Thank You for your response – Mahesh Gouda May 05 '22 at 10:16

1 Answers1

1

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
Mahesh Gouda
  • 118
  • 9