Find length of Array within JSON column

Question

I am trying to give a score on my query results. Each 'categories' array might contain many category integers, or it might be null. If it's null then it should be considered as found.

In the query below I'm searching for category 3 which is found. However I don't know how I can check if the Array categories is NULL so I can return 1 as well which will solve my problem.

I have tried:

WHERE Value IN(3) OR Value IS NULL)

WHERE Value IN(3) OR Value.length =0)

WHERE Value IN(3) OR Value = '')

etc....

For example

{"categories":[1,2,3],"distance":[130,300],"companysize":[2,4],"budget":[5000,1000000]}
{"categories":[],"distance":[60,200],"companysize":[1,2],"budget":[250,100]}

SQL

     SELECT * FROM (SELECT 
             (CASE WHEN EXISTS (Select * FROM OPENJSON(Preference,'$.categories') 
              WHERE Value IN(3) OR Value IS NULL)  -- < problem is here
              THEN 1 ELSE 0 END)
              matchscore, p.*
              FROM FinNotificationsPreferences p) x

Ryan B. · Accepted Answer · 2018-10-02 20:06:27Z

1

Try replacing your code:

Select * FROM OPENJSON(Preference,'$.categories') WHERE Value IN(3) OR Value IS NULL

with the following:

SELECT * FROM OPENJSON (Preference) WHERE [key] = 'categories' AND ([value] like '%3%' OR [value] = '[]')

The problem with your use of OPENJSON(Preference, '$.categories) is that when the array is empty, the OPENJSON table function returns no rows. This is indistinguishable from the case when the categories array has values, but none of them are '3'. So, we need to change our methodology.

By simply checking OPENJSON(Preference) and parsing the value for categories for a 3 or '[]' we either get 1 or 0 rows back. 1 row means our condition is found, 0 means its a miss.

NOTE: this parsing has some big shortcomings. If there's a category like 13, 23, 30, 300, etc... all these will match our %3% pattern. I leave it to you to harden the parsing as needed. I don't know your use cases.

answered Oct 2, 2018 at 20:06

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alwaysVBNET Over a year ago

I didn't like the like '%3%' part but your answer guided me to the solution. You can adjust your answer based on this: SELECT * FROM (SELECT ( (CASE WHEN EXISTS (SELECT * FROM OPENJSON (Preference) WHERE [key] = 'categories' AND (EXISTS (Select * FROM OPENJSON([value]) WHERE Value IN (3)) OR [value] = '[]' ) ) THEN 1 ELSE 0 END) ) matchscore, p.* FROM FinNotificationsPreferences p) x

Collectives™ on Stack Overflow

Find length of Array within JSON column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related