SQL Json null value openjson

Question

When I run this I get an error how do I take into consideration that the value could be null and it not throw a error ?

       declare @Concepts varchar(500)

    set @Concepts = '{"Concepts":null}';

   select [value] from openjson(@Concepts,'strict $.Concepts')

when it is not null

    set @Concepts = '{"Concepts": [4324,2342]}';

This is the error I am getting

Value referenced by JSON path is not an array or object and cannot be opened with OPENJSON.

What is the error you're getting? A null reference exception using the result of the query, or an error parsing/running the query? Please note that NULLs behave differently in SQL than most other languages (e.g. do not try X = NULL instead do X IS NULL and X IS NOT NULL instead). — Elaskanator, Commented Jan 16, 2019 at 15:04
Value referenced by JSON path is not an array or object and cannot be opened with OPENJSON. is the error understand they behave differently I am more talking about where there is a null in my JSON — Micah Armantrout, Commented Jan 16, 2019 at 15:06
What's Concepts supposed to be when it's not null? The correct answer depends on whether it's another JSON object or a scalar value. — Jeroen Mostert, Commented Jan 16, 2019 at 15:12

Micah Armantrout · Accepted Answer · 2019-01-16 15:28:34Z

2

I figured out taking the string 'strict' out of 'strict $.Concepts' solved my issue

 declare @Concepts varchar(500)
 set @Concepts = '{"Concepts":null}';
 select [value] from openjson(@Concepts,'$.Concepts')

answered Jan 16, 2019 at 15:28

Micah Armantrout

6,9076 gold badges41 silver badges69 bronze badges

Jeez. Now I feel silly... I missed this because I tested with {"Concepts":3}. In that case this approach won't work because it will not return any rows. (Neither will my suggested answer, though, which will henceforth be deleted.) The one thing of note is that the omission of strict means this query will succeed even if the Concepts property is missing entirely, which may or may not be what you want.
– Jeroen Mostert
Commented Jan 16, 2019 at 15:30
I appreciate your help in my scenario I don't care if it is missing entirely I am new to JSON and SQL server so any help I can get is appreciated.
– Micah Armantrout
Commented Jan 16, 2019 at 15:37

Add a comment |

1 Answer 1