CODEWITHSUNDEEP
sqljsonsql-server
Dmytro Kryvenko
Dmytro Kryvenko

Reputation: 343

Search by values in JSON column

I have a table, which contains column with JSON value, for instance:

id value
1 {"components": [{"label": "greeting 1", "components": [{"label": "hello", "value": 10}, {"label":"hi", "value": 20}]}]}
2 {"components": [{"label": "greeting 2", "components": [{"label": "aloha", "value": 30}, {"label":"hola", "value": 40}]}]}

I need to search for records, which contain some values in label or value or root or sub-components. I've started with query like:

    select DISTINCT id, value
from TABLE
         CROSS APPLY OPENJSON(JSON_QUERY(TABLE.value, '$.components'))
                              WITH (LABEL varchar(255) '$.label',
                                  VALUE varchar(255) '$.value') as jsonValues
WHERE jsonValues.LABEL like '%greeting%'
   OR jsonValues.VALUE like '%1%'

I've simplified JSON in example. In reallity it's more complex and have more fields and values, that should not be considered during search. That's why just simple LIKE by TABLE.value field doesn't suit. But stuck in search of nested components. Thanks in advance for help.

Upvotes: 0

Views: 101

Answers (1)

iamdave
iamdave

Reputation: 12243

Because you have many layers of nested JSON objects, you will need to specify to SQL Server that you need to dig through all those layers. That you have a label and components property for each layer is an added (and likely unecessary) complexity that also needs to be handled. In your 'simplified JSON' example there are 5 layers that need to be queried through, with care taken to apply filters at the appropriate levels to ensure all the nested properties are returned.

Suffice to say, this is less than ideal and would benefit significantly from a less convoluted JSON schema.

Query

declare @t table(id int,[value] nvarchar(1000));
insert into @t values
 (1,'{"components": [{"label": "greeting 1", "components": [{"label": "hello", "value": 10}, {"label":"hi", "value": 20}]}]}')
,(2,'{"components": [{"label": "greeting 2", "components": [{"label": "aloha", "value": 30}, {"label":"hola", "value": 40}]}]}')
;

select g.Greeting
      ,v.GreetingLabel
      ,v.GreetingValue
from @t as t
    cross apply openjson(t.[value],'$')
        with(Greetings nvarchar(max) 'strict $.components' as json) as j    -- Use of optional strict keyword requires that the property exists within the JSON object
    cross apply openjson(j.Greetings,'$')
        with(Greeting nvarchar(50) 'strict $.label'
            ,Components nvarchar(max) 'strict $.components' as json
            ) as g
    cross apply openjson(g.Components,'$')
        with(GreetingLabel nvarchar(50) 'strict $.label'
            ,GreetingValue int 'strict $.value'
            ) as v
where g.Greeting like 'greeting%'
    and v.GreetingValue = 10;

Output

Greeting GreetingLabel GreetingValue
greeting 1 hello 10

Upvotes: 1

Related Questions