Json extract

• Roust_m

  SSCoach

  Points: 17410

  More actions

  September 19, 2022 at 9:02 am

  #4089249

  Hi,

  I have a json files inside nvarchar(MAX) column, the structure is like below.

  I need a query which would show:

  SourceType, JournalID, SourceID, JournalLines.AccountType,  JournalLines.JournalLineID,  JournalLines.TaxName,  JournalLines.TrackingCategories

  The number of JournalLines in each journal can be diffrent, normally 5-10 of them.

  Any ideas?

  Thanks

  {
  "_Journals": [
  {
  "SourceType": 18,
  "JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
  "SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
  "JournalLines": [
  {
  "AccountType": 3,
  "JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
  "TaxName": "Some tax name",
  "TrackingCategories": []
  },
  {
  "AccountType": 3,
  "JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
  "TaxName": "text",
  "TrackingCategories": []
  }
  ]
  },
  {
  "SourceType": 18,
  "JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
  "SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
  "JournalLines": [
  {
  "AccountType": 3,
  "JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
  "TaxName": "some text",
  "TrackingCategories": []
  },
  {
  "AccountType": 11,
  "JournalLineID": "sdfs-sdfsd-sdf-sdfd-sdfds",
  "TaxName": "some text",
  "TrackingCategories": []
  },
  {
  "AccountType": 2,
  "JournalLineID": "sdsfdsfd-sdf-sdf-sdfdsf-sdfsdf",
  "TaxName": "some text",
  "TrackingCategories": []
  }
  ]
  }
  ]
  }

  • This topic was modified 2 years, 2 months ago by  Roust_m.
  • This topic was modified 2 years, 2 months ago by  Roust_m.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 19, 2022 at 10:46 am

  #4089285

  declare @mytable table(mydata nvarchar(max));
  
  insert into @mytable(mydata)
  values(N'
  {
  "_Journals": [
  {
  "SourceType": 18,
  "JournalID": "sdfdsf-fdfs-dfdf-dfdfd-dfdsfsfdd",
  "SourceID": "sdfsdf-sdfds-sdf-dfd-sdfsdf",
  "JournalLines": [
  {
  "AccountType": 3,
  "JournalLineID": "sdfsdfsd-sdfsdf-sdfsd-sdfsdf-sdfsdf",
  "TaxName": "Some tax name",
  "TrackingCategories": []
  },
  {
  "AccountType": 3,
  "JournalLineID": "sdfsdfsdf-sdfsdf-sdf-sdf-sdf",
  "TaxName": "text",
  "TrackingCategories": []
  }
  ]
  },
  {
  "SourceType": 18,
  "JournalID": "sdfsd-sdfd-sdfd-sdf-dfdsfd",
  "SourceID": "61da35a7-4f8c-4d3a-9a18-e531734b419c",
  "JournalLines": [
  {
  "AccountType": 3,
  "JournalLineID": "sdfdsf-dsfds-sdf-sdfsd-sdfd",
  "TaxName": "some text",
  "TrackingCategories": []
  },
  {
  "AccountType": 11,
  "JournalLineID": "sdfs-sdfsd-sdf-sdfd-sdfds",
  "TaxName": "some text",
  "TrackingCategories": []
  },
  {
  "AccountType": 2,
  "JournalLineID": "sdsfdsfd-sdf-sdf-sdfdsf-sdfsdf",
  "TaxName": "some text",
  "TrackingCategories": []
  }
  ]
  }
  ]
  }
  ');
  
  
  select j2.SourceType,
         j2.JournalID,
         j2.SourceID,
     j3.AccountType,
     j3.JournalLineID,
     j3.TaxName,
     j3.TrackingCategories
  from @mytable t
  cross apply openjson(t.mydata, '$._Journals') j1
  cross apply openjson(j1.value)
  with (SourceType   int  '$.SourceType',
        JournalID   varchar(50)  '$.JournalID',
        SourceID   varchar(50)  '$.SourceID',
    JournalLines nvarchar(max) '$.JournalLines' as json) j2
  outer apply openjson(j2.JournalLines)
  with (AccountType   int  '$.AccountType',
        JournalLineID   varchar(50)  '$.JournalLineID',
        TaxName   varchar(50)  '$.TaxName',
    TrackingCategories nvarchar(max) '$.TrackingCategories' as json) j3;
  

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Roust_m

  SSCoach

  Points: 17410

  More actions

  September 19, 2022 at 12:17 pm

  #4089341

  Brilliant!  Thanks!