SQL - JSON Cell Query

Question

im new to JSON - Code and i have a cell in the SQL Server at my work from a database that is in JSON-format.

Here is the JSON String in the database:

{
   "fields":{
      "field":[
         {
            "result":0,
            "isrequired":"true",
            "name":"empfohlene Garantiebewertung",
            "type":"eEnumeration",
            "facets":[
               {
                  "facet":[
                     {
                        "id":0,
                        "type":"Enumeration",
                        "value":"keine Garantie;Garantie ;Kulanz"
                     },
                     {
                        "id":1,
                        "type":"choicetype",
                        "value":"singlechoice"
                     },
                     {
                        "id":2,
                        "type":"ErrorRange",
                        "value":"false;false;false"
                     }
                  ]
               }
            ]
         }
      ]
   }
}

The result 0 tells that the first value "keine Garantie" has been selected in our database. Can i display directly in one cell in the SQL query wich value has been selected?

Here is a example of the best result i could get with my SQL knowledge:

SELECT
JSON_VALUE (REPLACE(Facet.[value], '[', ''), '$.facet.value') as 'Values',
Result.[value] as 'Result'

FROM
CROSS APPLY OPENJSON(JSON_query(JSONCELL, '$.fields.field'))  as Result2
CROSS APPLY OPENJSON(Result2.[value], '$')  as Result
CROSS APPLY OPENJSON(JSON_query(JSONCELL, '$.fields.field'))  as Facet2
CROSS APPLY OPENJSON(Facet2.[value], '$')  as Facet

RESULT:

Values "keine Garantie;Garantie;Kulanz"

Result "0"

EDIT: Im after the result "keine Garantie". On the Website wich the data is coming from i can choose from "keine Garantie", "Garantie", "Kulanz". The result 0 is telling that the user choosed "keine Garantie".

Thank you in advance for your help and your answers!

Does this answer your question? [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/q/19449492/2029983) — Thom A, Oct 15 '20 at 09:50
For future readers notes, the reason for the (apparent) unrelated duplicate is due to the *real* problem being exposed in the comments under my answer. — Thom A, Oct 15 '20 at 09:54

score 0 · Accepted Answer · answered Oct 15 '20 at 09:33

Is this what you're after?

DECLARE @JSON nvarchar(MAX) = N'{
   "fields":{
      "field":[
         {
            "result":0,
            "isrequired":"true",
            "name":"empfohlene Garantiebewertung",
            "type":"eEnumeration",
            "facets":[
               {
                  "facet":[
                     {
                        "id":0,
                        "type":"Enumeration",
                        "value":"keine Garantie;Garantie ;Kulanz"
                     },
                     {
                        "id":1,
                        "type":"choicetype",
                        "value":"singlechoice"
                     },
                     {
                        "id":2,
                        "type":"ErrorRange",
                        "value":"false;false;false"
                     }
                  ]
               }
            ]
         }
      ]
   }
}';

SELECT F.result,
       FFF.[value]
FROM (VALUES(@JSON))V(J)
     CROSS APPLY OPENJSON(V.J,'$.fields.field') 
                 WITH (result int,
                       facets nvarchar(MAX) AS JSON) F
     CROSS APPLY OPENJSON(F.facets)
                 WITH (facet nvarchar(MAX) AS JSON) FF
     CROSS APPLY OPENJSON(FF.facet)
                 WITH(id int,
                      value nvarchar(4000)) FFF
WHERE F.result = 0
  AND FFF.id = 0;

Hello, no im after the result "keine Garantie". On the Website wich the data is coming from i can choose from "keine Garantie", "Garantie", "Kulanz". The Value 0 is telling that the user choosed "keine Garantie". — professorfichte, Oct 15 '20 at 09:48
Your question says you are after the whole string, @professorfichte. YOu state *"RESULT: Values "keine Garantie;Garantie;Kulanz""* — Thom A, Oct 15 '20 at 09:49
So what you are *really* asking if how to get the *n*th value from a delimited string, @professorfichte ...? — Thom A, Oct 15 '20 at 09:50
Yes thats right, sorry that i did not ask my question accurately ,my english is not the best. — professorfichte, Oct 15 '20 at 09:53

SQL - JSON Cell Query

1 Answers1