4

I have a JSON column in a table that I'm parsing out to create a view. I have 120 columns in the JSON string and it produces about 1750 rows of data. Problem is, the query takes 17 seconds to run and it's a basic query like the one below (with 120 columns being selected):

SELECT JSON_VALUE(json, '$.member.firstname') as MemberFirstName
     , JSON_VALUE(json, '$.member.lastname') as MemberLastName
     , JSON_VALUE(json, '$.member.phone') as MemberPhone
  FROM foobar

I know this table is going to grow and I'm hoping to get the performance better on it before I create a view with the code. I'm reading some articles about creating a computed columns or use DBCC PAGE function but I'm wondering if there's a less intensive way to get what we're looking for.

Improve this question
4
  • Why are you storing data as JSON in the first place? Commented Feb 5, 2019 at 19:20
  • I'm not, our ORG is :) This has been in place longer than I've been employed here, just trying to provide a solution to our team Commented Feb 5, 2019 at 19:49
  • openjson would probably perform better for you. Can you provide a few sample rows of what's in your json column or at least the structure? Commented Feb 5, 2019 at 20:18
  • Tim's suggestion and answer greatly helps, but the real problem is a bad design. My rule: don't save as JSON if you need to query the content later. Persist them in relational form or don't use a relational database. I understand you inherited it, but you could work to resolve the design problem. As a hack, if the data is not changed often, you could extract the data after changes and store in a table for quick querying or as you stated computed columns. Commented Feb 5, 2019 at 21:30

1 Answer 1

Reset to default
9

I made some some assumptions of what the structure of your json looks like based on your query above.

OPENJSON provides a rowset view over the JSON document basically treating it as a table, whereas JSON_VALUE() is extracting a value from the JSON string which means it's doing that over each column for every row and will be more expensive.

Here an example so you can see the different between using JSON_VALUE() compared to OPENJSON(). OPENJSON() will require a larger query, but will perform better over all.

--Setup some test data so we can see performance with a similar number of columns you're dealing with.
CREATE TABLE [#foobar]
    (
        [jsondata] NVARCHAR(MAX)
    );

--load our temp table with 2000 test records
DECLARE @Counter INT = 1;
WHILE @Counter <= 2000
    BEGIN
        INSERT INTO [#foobar] (
                                  [jsondata]
                              )
        VALUES (
            N'{
  "member": {
    "firstname": "John",
    "lastname": "Smith",
    "phone": "1234566789",
    "column1":  "testdata1"
    ,"column2": "testdata2"
    ,"column3": "testdata3"
    ,"column4": "testdata4"
    ,"column5": "testdata5"
    ,"column6": "testdata6"
    ,"column7": "testdata7"
    ,"column8": "testdata8"
    ,"column9": "testdata9"
    ,"column10": "testdata10"
    ,"column11": "testdata11"
    ,"column12": "testdata12"
    ,"column13": "testdata13"
    ,"column14": "testdata14"
    ,"column15": "testdata15"
    ,"column16": "testdata16"
    ,"column17": "testdata17"
    ,"column18": "testdata18"
    ,"column19": "testdata19"
    ,"column20": "testdata20"
    ,"column21": "testdata21"
    ,"column22": "testdata22"
    ,"column23": "testdata23"
    ,"column24": "testdata24"
    ,"column25": "testdata25"
    ,"column26": "testdata26"
    ,"column27": "testdata27"
    ,"column28": "testdata28"
    ,"column29": "testdata29"
    ,"column30": "testdata30"
    ,"column31": "testdata31"
    ,"column32": "testdata32"
    ,"column33": "testdata33"
    ,"column34": "testdata34"
    ,"column35": "testdata35"
    ,"column36": "testdata36"
    ,"column37": "testdata37"
    ,"column38": "testdata38"
    ,"column39": "testdata39"
    ,"column40": "testdata40"
    ,"column41": "testdata41"
    ,"column42": "testdata42"
    ,"column43": "testdata43"
    ,"column44": "testdata44"
    ,"column45": "testdata45"
    ,"column46": "testdata46"
    ,"column47": "testdata47"
    ,"column48": "testdata48"
    ,"column49": "testdata49"
    ,"column50": "testdata50"
    ,"column51": "testdata51"
    ,"column52": "testdata52"
    ,"column53": "testdata53"
    ,"column54": "testdata54"
    ,"column55": "testdata55"
    ,"column56": "testdata56"
    ,"column57": "testdata57"
    ,"column58": "testdata58"
    ,"column59": "testdata59"
    ,"column60": "testdata60"
    ,"column61": "testdata61"
    ,"column62": "testdata62"
    ,"column63": "testdata63"
    ,"column64": "testdata64"
    ,"column65": "testdata65"
    ,"column66": "testdata66"
    ,"column67": "testdata67"
    ,"column68": "testdata68"
    ,"column69": "testdata69"
    ,"column70": "testdata70"
    ,"column71": "testdata71"
    ,"column72": "testdata72"
    ,"column73": "testdata73"
    ,"column74": "testdata74"
    ,"column75": "testdata75"
    ,"column76": "testdata76"
    ,"column77": "testdata77"
    ,"column78": "testdata78"
    ,"column79": "testdata79"
    ,"column80": "testdata80"
    ,"column81": "testdata81"
    ,"column82": "testdata82"
    ,"column83": "testdata83"
    ,"column84": "testdata84"
    ,"column85": "testdata85"
    ,"column86": "testdata86"
    ,"column87": "testdata87"
    ,"column88": "testdata88"
    ,"column89": "testdata89"
    ,"column90": "testdata90"
    ,"column91": "testdata91"
    ,"column92": "testdata92"
    ,"column93": "testdata93"
    ,"column94": "testdata94"
    ,"column95": "testdata95"
    ,"column96": "testdata96"
    ,"column97": "testdata97"
    ,"column98": "testdata98"
    ,"column99": "testdata99"
    ,"column100": "testdata100"
    ,"column101": "testdata101"
    ,"column102": "testdata102"
    ,"column103": "testdata103"
    ,"column104": "testdata104"
    ,"column105": "testdata105"
    ,"column106": "testdata106"
    ,"column107": "testdata107"
    ,"column108": "testdata108"
    ,"column109": "testdata109"
    ,"column110": "testdata110"
    ,"column111": "testdata111"
    ,"column112": "testdata112"
    ,"column113": "testdata113"
    ,"column114": "testdata114"
    ,"column115": "testdata115"
    ,"column116": "testdata116"
    ,"column117": "testdata117"
    ,"column118": "testdata118"
    ,"column119": "testdata119"
    ,"column120": "testdata120"
  }
}'
        );
        SET @Counter = @Counter + 1;
    END;

Running your original query using JSON_VALUE() I was somewhere in the 13-15 second range:

SELECT JSON_VALUE([jsondata], '$.member.firstname') AS [MemberFirstName]
     , JSON_VALUE([jsondata], '$.member.lastname') AS [MemberLastName]
     , JSON_VALUE([jsondata], '$.member.phone') AS [MemberPhone]
     , JSON_VALUE([jsondata], '$.member.column1') AS [Column1]
     , JSON_VALUE([jsondata], '$.member.column2') AS [Column2]
     , JSON_VALUE([jsondata], '$.member.column3') AS [Column3]
     , JSON_VALUE([jsondata], '$.member.column4') AS [Column4]
     , JSON_VALUE([jsondata], '$.member.column5') AS [Column5]
     , JSON_VALUE([jsondata], '$.member.column6') AS [Column6]
     , JSON_VALUE([jsondata], '$.member.column7') AS [Column7]
     , JSON_VALUE([jsondata], '$.member.column8') AS [Column8]
     , JSON_VALUE([jsondata], '$.member.column9') AS [Column9]
     , JSON_VALUE([jsondata], '$.member.column10') AS [Column10]
     , JSON_VALUE([jsondata], '$.member.column11') AS [Column11]
     , JSON_VALUE([jsondata], '$.member.column12') AS [Column12]
     , JSON_VALUE([jsondata], '$.member.column13') AS [Column13]
     , JSON_VALUE([jsondata], '$.member.column14') AS [Column14]
     , JSON_VALUE([jsondata], '$.member.column15') AS [Column15]
     , JSON_VALUE([jsondata], '$.member.column16') AS [Column16]
     , JSON_VALUE([jsondata], '$.member.column17') AS [Column17]
     , JSON_VALUE([jsondata], '$.member.column18') AS [Column18]
     , JSON_VALUE([jsondata], '$.member.column19') AS [Column19]
     , JSON_VALUE([jsondata], '$.member.column20') AS [Column20]
     , JSON_VALUE([jsondata], '$.member.column21') AS [Column21]
     , JSON_VALUE([jsondata], '$.member.column22') AS [Column22]
     , JSON_VALUE([jsondata], '$.member.column23') AS [Column23]
     , JSON_VALUE([jsondata], '$.member.column24') AS [Column24]
     , JSON_VALUE([jsondata], '$.member.column25') AS [Column25]
     , JSON_VALUE([jsondata], '$.member.column26') AS [Column26]
     , JSON_VALUE([jsondata], '$.member.column27') AS [Column27]
     , JSON_VALUE([jsondata], '$.member.column28') AS [Column28]
     , JSON_VALUE([jsondata], '$.member.column29') AS [Column29]
     , JSON_VALUE([jsondata], '$.member.column30') AS [Column30]
     , JSON_VALUE([jsondata], '$.member.column31') AS [Column31]
     , JSON_VALUE([jsondata], '$.member.column32') AS [Column32]
     , JSON_VALUE([jsondata], '$.member.column33') AS [Column33]
     , JSON_VALUE([jsondata], '$.member.column34') AS [Column34]
     , JSON_VALUE([jsondata], '$.member.column35') AS [Column35]
     , JSON_VALUE([jsondata], '$.member.column36') AS [Column36]
     , JSON_VALUE([jsondata], '$.member.column37') AS [Column37]
     , JSON_VALUE([jsondata], '$.member.column38') AS [Column38]
     , JSON_VALUE([jsondata], '$.member.column39') AS [Column39]
     , JSON_VALUE([jsondata], '$.member.column40') AS [Column40]
     , JSON_VALUE([jsondata], '$.member.column41') AS [Column41]
     , JSON_VALUE([jsondata], '$.member.column42') AS [Column42]
     , JSON_VALUE([jsondata], '$.member.column43') AS [Column43]
     , JSON_VALUE([jsondata], '$.member.column44') AS [Column44]
     , JSON_VALUE([jsondata], '$.member.column45') AS [Column45]
     , JSON_VALUE([jsondata], '$.member.column46') AS [Column46]
     , JSON_VALUE([jsondata], '$.member.column47') AS [Column47]
     , JSON_VALUE([jsondata], '$.member.column48') AS [Column48]
     , JSON_VALUE([jsondata], '$.member.column49') AS [Column49]
     , JSON_VALUE([jsondata], '$.member.column50') AS [Column50]
     , JSON_VALUE([jsondata], '$.member.column51') AS [Column51]
     , JSON_VALUE([jsondata], '$.member.column52') AS [Column52]
     , JSON_VALUE([jsondata], '$.member.column53') AS [Column53]
     , JSON_VALUE([jsondata], '$.member.column54') AS [Column54]
     , JSON_VALUE([jsondata], '$.member.column55') AS [Column55]
     , JSON_VALUE([jsondata], '$.member.column56') AS [Column56]
     , JSON_VALUE([jsondata], '$.member.column57') AS [Column57]
     , JSON_VALUE([jsondata], '$.member.column58') AS [Column58]
     , JSON_VALUE([jsondata], '$.member.column59') AS [Column59]
     , JSON_VALUE([jsondata], '$.member.column60') AS [Column60]
     , JSON_VALUE([jsondata], '$.member.column61') AS [Column61]
     , JSON_VALUE([jsondata], '$.member.column62') AS [Column62]
     , JSON_VALUE([jsondata], '$.member.column63') AS [Column63]
     , JSON_VALUE([jsondata], '$.member.column64') AS [Column64]
     , JSON_VALUE([jsondata], '$.member.column65') AS [Column65]
     , JSON_VALUE([jsondata], '$.member.column66') AS [Column66]
     , JSON_VALUE([jsondata], '$.member.column67') AS [Column67]
     , JSON_VALUE([jsondata], '$.member.column68') AS [Column68]
     , JSON_VALUE([jsondata], '$.member.column69') AS [Column69]
     , JSON_VALUE([jsondata], '$.member.column70') AS [Column70]
     , JSON_VALUE([jsondata], '$.member.column71') AS [Column71]
     , JSON_VALUE([jsondata], '$.member.column72') AS [Column72]
     , JSON_VALUE([jsondata], '$.member.column73') AS [Column73]
     , JSON_VALUE([jsondata], '$.member.column74') AS [Column74]
     , JSON_VALUE([jsondata], '$.member.column75') AS [Column75]
     , JSON_VALUE([jsondata], '$.member.column76') AS [Column76]
     , JSON_VALUE([jsondata], '$.member.column77') AS [Column77]
     , JSON_VALUE([jsondata], '$.member.column78') AS [Column78]
     , JSON_VALUE([jsondata], '$.member.column79') AS [Column79]
     , JSON_VALUE([jsondata], '$.member.column80') AS [Column80]
     , JSON_VALUE([jsondata], '$.member.column81') AS [Column81]
     , JSON_VALUE([jsondata], '$.member.column82') AS [Column82]
     , JSON_VALUE([jsondata], '$.member.column83') AS [Column83]
     , JSON_VALUE([jsondata], '$.member.column84') AS [Column84]
     , JSON_VALUE([jsondata], '$.member.column85') AS [Column85]
     , JSON_VALUE([jsondata], '$.member.column86') AS [Column86]
     , JSON_VALUE([jsondata], '$.member.column87') AS [Column87]
     , JSON_VALUE([jsondata], '$.member.column88') AS [Column88]
     , JSON_VALUE([jsondata], '$.member.column89') AS [Column89]
     , JSON_VALUE([jsondata], '$.member.column90') AS [Column90]
     , JSON_VALUE([jsondata], '$.member.column91') AS [Column91]
     , JSON_VALUE([jsondata], '$.member.column92') AS [Column92]
     , JSON_VALUE([jsondata], '$.member.column93') AS [Column93]
     , JSON_VALUE([jsondata], '$.member.column94') AS [Column94]
     , JSON_VALUE([jsondata], '$.member.column95') AS [Column95]
     , JSON_VALUE([jsondata], '$.member.column96') AS [Column96]
     , JSON_VALUE([jsondata], '$.member.column97') AS [Column97]
     , JSON_VALUE([jsondata], '$.member.column98') AS [Column98]
     , JSON_VALUE([jsondata], '$.member.column99') AS [Column99]
     , JSON_VALUE([jsondata], '$.member.column100') AS [Column100]
     , JSON_VALUE([jsondata], '$.member.column101') AS [Column101]
     , JSON_VALUE([jsondata], '$.member.column102') AS [Column102]
     , JSON_VALUE([jsondata], '$.member.column103') AS [Column103]
     , JSON_VALUE([jsondata], '$.member.column104') AS [Column104]
     , JSON_VALUE([jsondata], '$.member.column105') AS [Column105]
     , JSON_VALUE([jsondata], '$.member.column106') AS [Column106]
     , JSON_VALUE([jsondata], '$.member.column107') AS [Column107]
     , JSON_VALUE([jsondata], '$.member.column108') AS [Column108]
     , JSON_VALUE([jsondata], '$.member.column109') AS [Column109]
     , JSON_VALUE([jsondata], '$.member.column110') AS [Column110]
     , JSON_VALUE([jsondata], '$.member.column111') AS [Column111]
     , JSON_VALUE([jsondata], '$.member.column112') AS [Column112]
     , JSON_VALUE([jsondata], '$.member.column113') AS [Column113]
     , JSON_VALUE([jsondata], '$.member.column114') AS [Column114]
     , JSON_VALUE([jsondata], '$.member.column115') AS [Column115]
     , JSON_VALUE([jsondata], '$.member.column116') AS [Column116]
     , JSON_VALUE([jsondata], '$.member.column117') AS [Column117]
     , JSON_VALUE([jsondata], '$.member.column118') AS [Column118]
     , JSON_VALUE([jsondata], '$.member.column119') AS [Column119]
     , JSON_VALUE([jsondata], '$.member.column120') AS [Column120]
FROM   [#foobar];

Here's an example of what that looks like rewritten to us OPENJSON() using a WITH clause. This was consistently giving me results in 2-3 seconds.

--Test using OPENJSON()
--You will want to list each column in your select from the with clause
--a lot better peformance then doing a select *
SELECT [b].[MemberFirstName]
     , [b].[MemberLastName]
     , [b].[MemberPhone]
     , [b].[Column1]
     , [b].[Column2]
     , [b].[Column3]
     , [b].[Column4]
     , [b].[Column5]
     , [b].[Column6]
     , [b].[Column7]
     , [b].[Column8]
     , [b].[Column9]
     , [b].[Column10]
     , [b].[Column11]
     , [b].[Column12]
     , [b].[Column13]
     , [b].[Column14]
     , [b].[Column15]
     , [b].[Column16]
     , [b].[Column17]
     , [b].[Column18]
     , [b].[Column19]
     , [b].[Column20]
     , [b].[Column21]
     , [b].[Column22]
     , [b].[Column23]
     , [b].[Column24]
     , [b].[Column25]
     , [b].[Column26]
     , [b].[Column27]
     , [b].[Column28]
     , [b].[Column29]
     , [b].[Column30]
     , [b].[Column31]
     , [b].[Column32]
     , [b].[Column33]
     , [b].[Column34]
     , [b].[Column35]
     , [b].[Column36]
     , [b].[Column37]
     , [b].[Column38]
     , [b].[Column39]
     , [b].[Column40]
     , [b].[Column41]
     , [b].[Column42]
     , [b].[Column43]
     , [b].[Column44]
     , [b].[Column45]
     , [b].[Column46]
     , [b].[Column47]
     , [b].[Column48]
     , [b].[Column49]
     , [b].[Column50]
     , [b].[Column51]
     , [b].[Column52]
     , [b].[Column53]
     , [b].[Column54]
     , [b].[Column55]
     , [b].[Column56]
     , [b].[Column57]
     , [b].[Column58]
     , [b].[Column59]
     , [b].[Column60]
     , [b].[Column61]
     , [b].[Column62]
     , [b].[Column63]
     , [b].[Column64]
     , [b].[Column65]
     , [b].[Column66]
     , [b].[Column67]
     , [b].[Column68]
     , [b].[Column69]
     , [b].[Column70]
     , [b].[Column71]
     , [b].[Column72]
     , [b].[Column73]
     , [b].[Column74]
     , [b].[Column75]
     , [b].[Column76]
     , [b].[Column77]
     , [b].[Column78]
     , [b].[Column79]
     , [b].[Column80]
     , [b].[Column81]
     , [b].[Column82]
     , [b].[Column83]
     , [b].[Column84]
     , [b].[Column85]
     , [b].[Column86]
     , [b].[Column87]
     , [b].[Column88]
     , [b].[Column89]
     , [b].[Column90]
     , [b].[Column91]
     , [b].[Column92]
     , [b].[Column93]
     , [b].[Column94]
     , [b].[Column95]
     , [b].[Column96]
     , [b].[Column97]
     , [b].[Column98]
     , [b].[Column99]
     , [b].[Column100]
     , [b].[Column101]
     , [b].[Column102]
     , [b].[Column103]
     , [b].[Column104]
     , [b].[Column105]
     , [b].[Column106]
     , [b].[Column107]
     , [b].[Column108]
     , [b].[Column109]
     , [b].[Column110]
     , [b].[Column111]
     , [b].[Column112]
     , [b].[Column113]
     , [b].[Column114]
     , [b].[Column115]
     , [b].[Column116]
     , [b].[Column117]
     , [b].[Column118]
     , [b].[Column119]
     , [b].[Column120]
FROM   [#foobar] [a]
CROSS APPLY
       OPENJSON([a].[jsondata], '$.member')
           WITH (
                    [MemberFirstName] NVARCHAR(200) '$.firstname'
                  , [MemberLastName] NVARCHAR(200) '$.lastname'
                  , [MemberPhone] NVARCHAR(200) '$.phone'
                  , [Column1] NVARCHAR(200) '$.column1'
                  , [Column2] NVARCHAR(200) '$.column2'
                  , [Column3] NVARCHAR(200) '$.column3'
                  , [Column4] NVARCHAR(200) '$.column4'
                  , [Column5] NVARCHAR(200) '$.column5'
                  , [Column6] NVARCHAR(200) '$.column6'
                  , [Column7] NVARCHAR(200) '$.column7'
                  , [Column8] NVARCHAR(200) '$.column8'
                  , [Column9] NVARCHAR(200) '$.column9'
                  , [Column10] NVARCHAR(200) '$.column10'
                  , [Column11] NVARCHAR(200) '$.column11'
                  , [Column12] NVARCHAR(200) '$.column12'
                  , [Column13] NVARCHAR(200) '$.column13'
                  , [Column14] NVARCHAR(200) '$.column14'
                  , [Column15] NVARCHAR(200) '$.column15'
                  , [Column16] NVARCHAR(200) '$.column16'
                  , [Column17] NVARCHAR(200) '$.column17'
                  , [Column18] NVARCHAR(200) '$.column18'
                  , [Column19] NVARCHAR(200) '$.column19'
                  , [Column20] NVARCHAR(200) '$.column20'
                  , [Column21] NVARCHAR(200) '$.column21'
                  , [Column22] NVARCHAR(200) '$.column22'
                  , [Column23] NVARCHAR(200) '$.column23'
                  , [Column24] NVARCHAR(200) '$.column24'
                  , [Column25] NVARCHAR(200) '$.column25'
                  , [Column26] NVARCHAR(200) '$.column26'
                  , [Column27] NVARCHAR(200) '$.column27'
                  , [Column28] NVARCHAR(200) '$.column28'
                  , [Column29] NVARCHAR(200) '$.column29'
                  , [Column30] NVARCHAR(200) '$.column30'
                  , [Column31] NVARCHAR(200) '$.column31'
                  , [Column32] NVARCHAR(200) '$.column32'
                  , [Column33] NVARCHAR(200) '$.column33'
                  , [Column34] NVARCHAR(200) '$.column34'
                  , [Column35] NVARCHAR(200) '$.column35'
                  , [Column36] NVARCHAR(200) '$.column36'
                  , [Column37] NVARCHAR(200) '$.column37'
                  , [Column38] NVARCHAR(200) '$.column38'
                  , [Column39] NVARCHAR(200) '$.column39'
                  , [Column40] NVARCHAR(200) '$.column40'
                  , [Column41] NVARCHAR(200) '$.column41'
                  , [Column42] NVARCHAR(200) '$.column42'
                  , [Column43] NVARCHAR(200) '$.column43'
                  , [Column44] NVARCHAR(200) '$.column44'
                  , [Column45] NVARCHAR(200) '$.column45'
                  , [Column46] NVARCHAR(200) '$.column46'
                  , [Column47] NVARCHAR(200) '$.column47'
                  , [Column48] NVARCHAR(200) '$.column48'
                  , [Column49] NVARCHAR(200) '$.column49'
                  , [Column50] NVARCHAR(200) '$.column50'
                  , [Column51] NVARCHAR(200) '$.column51'
                  , [Column52] NVARCHAR(200) '$.column52'
                  , [Column53] NVARCHAR(200) '$.column53'
                  , [Column54] NVARCHAR(200) '$.column54'
                  , [Column55] NVARCHAR(200) '$.column55'
                  , [Column56] NVARCHAR(200) '$.column56'
                  , [Column57] NVARCHAR(200) '$.column57'
                  , [Column58] NVARCHAR(200) '$.column58'
                  , [Column59] NVARCHAR(200) '$.column59'
                  , [Column60] NVARCHAR(200) '$.column60'
                  , [Column61] NVARCHAR(200) '$.column61'
                  , [Column62] NVARCHAR(200) '$.column62'
                  , [Column63] NVARCHAR(200) '$.column63'
                  , [Column64] NVARCHAR(200) '$.column64'
                  , [Column65] NVARCHAR(200) '$.column65'
                  , [Column66] NVARCHAR(200) '$.column66'
                  , [Column67] NVARCHAR(200) '$.column67'
                  , [Column68] NVARCHAR(200) '$.column68'
                  , [Column69] NVARCHAR(200) '$.column69'
                  , [Column70] NVARCHAR(200) '$.column70'
                  , [Column71] NVARCHAR(200) '$.column71'
                  , [Column72] NVARCHAR(200) '$.column72'
                  , [Column73] NVARCHAR(200) '$.column73'
                  , [Column74] NVARCHAR(200) '$.column74'
                  , [Column75] NVARCHAR(200) '$.column75'
                  , [Column76] NVARCHAR(200) '$.column76'
                  , [Column77] NVARCHAR(200) '$.column77'
                  , [Column78] NVARCHAR(200) '$.column78'
                  , [Column79] NVARCHAR(200) '$.column79'
                  , [Column80] NVARCHAR(200) '$.column80'
                  , [Column81] NVARCHAR(200) '$.column81'
                  , [Column82] NVARCHAR(200) '$.column82'
                  , [Column83] NVARCHAR(200) '$.column83'
                  , [Column84] NVARCHAR(200) '$.column84'
                  , [Column85] NVARCHAR(200) '$.column85'
                  , [Column86] NVARCHAR(200) '$.column86'
                  , [Column87] NVARCHAR(200) '$.column87'
                  , [Column88] NVARCHAR(200) '$.column88'
                  , [Column89] NVARCHAR(200) '$.column89'
                  , [Column90] NVARCHAR(200) '$.column90'
                  , [Column91] NVARCHAR(200) '$.column91'
                  , [Column92] NVARCHAR(200) '$.column92'
                  , [Column93] NVARCHAR(200) '$.column93'
                  , [Column94] NVARCHAR(200) '$.column94'
                  , [Column95] NVARCHAR(200) '$.column95'
                  , [Column96] NVARCHAR(200) '$.column96'
                  , [Column97] NVARCHAR(200) '$.column97'
                  , [Column98] NVARCHAR(200) '$.column98'
                  , [Column99] NVARCHAR(200) '$.column99'
                  , [Column100] NVARCHAR(200) '$.column100'
                  , [Column101] NVARCHAR(200) '$.column101'
                  , [Column102] NVARCHAR(200) '$.column102'
                  , [Column103] NVARCHAR(200) '$.column103'
                  , [Column104] NVARCHAR(200) '$.column104'
                  , [Column105] NVARCHAR(200) '$.column105'
                  , [Column106] NVARCHAR(200) '$.column106'
                  , [Column107] NVARCHAR(200) '$.column107'
                  , [Column108] NVARCHAR(200) '$.column108'
                  , [Column109] NVARCHAR(200) '$.column109'
                  , [Column110] NVARCHAR(200) '$.column110'
                  , [Column111] NVARCHAR(200) '$.column111'
                  , [Column112] NVARCHAR(200) '$.column112'
                  , [Column113] NVARCHAR(200) '$.column113'
                  , [Column114] NVARCHAR(200) '$.column114'
                  , [Column115] NVARCHAR(200) '$.column115'
                  , [Column116] NVARCHAR(200) '$.column116'
                  , [Column117] NVARCHAR(200) '$.column117'
                  , [Column118] NVARCHAR(200) '$.column118'
                  , [Column119] NVARCHAR(200) '$.column119'
                  , [Column120] NVARCHAR(200) '$.column120'
                ) AS [b];
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for putting this out there. It makes more sense than the Microsoft examples for some reason. Here's another question. If "member" isn't the only element in the json string, do I just have another cross apply when needed? I have other elements such as "detail" and "submitter", and others
@jdids basically, yes. It's ultimately going to depend on your structure and if "detail" and or "submitter" are arrays or part of "member" or not with where and how another cross apply comes into play. If the others are at the same level as member, just another cross apply should get you there. It they are within member, you'll have that as another column in member and then your additional cross apply would use that column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.