Parsing with OPENJSON

Question

I am trying to take the response from a GET call and load it into SQL Server via OPENJSON, but I'm having trouble parsing the response.

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "LookupServiceType": "GetAssetValues",
  "Items": [
    {
      "id": "19676",
      "value": "{\"AssetCode\":\"TDAACC\",\"Symbol\":null,\"Issue\":\"ACCOUNT #49\",\"Issuer\":\"TD AMERITRADE\"}"
    },
    {
      "id": "19677",
      "value": "{\"AssetCode\":\"RE100\",\"Symbol\":null,\"Issue\":\"APN: 057\",\"Issuer\":\"SAN ANTONIO TX 78212\"}"
    },
    {
      "id": "19908",
      "value": "{\"AssetCode\":\"NALIPO\",\"Symbol\":null,\"Issue\":\"POLICY # L0472\",\"Issuer\":\"NATIONWIDE LIFE\"}"
    }
  ]
}';

I've tried many variations, but I just can't get it right. Here are a few things I've tried. Any suggestions would be appreciated.

SELECT *
FROM OPENJSON(@json, '$.Items')  
  WITH (
    Items int '$.id',
    value NVARCHAR(MAX) '$.value',
    AssetCode NVARCHAR(50) '$.value.AssetCode',
    Symbol NVARCHAR(50) '$.value.Symbol',
    Issue NVARCHAR(50) '$.value.Issue',
    Issuer NVARCHAR(50) '$.value.Issuer'
  );

SELECT id,AssetCode,Symbol,Issue, Issuer
FROM OPENJSON(@json)  
  WITH (
    Items NVARCHAR(MAX) '$.Items' AS JSON
  )
OUTER APPLY OPENJSON(Items)
  WITH (
  id INT 'strict $.id',
  value NVARCHAR(MAX) '$.value' AS JSON
  )
  OUTER APPLY OPENJSON(value)
  WITH (
  AssetCode NVARCHAR(50) '$.AssetCode',
  Symbol NVARCHAR(50) '$.Symbol',
  Issue NVARCHAR(50) '$.Issue',
  Issuer NVARCHAR(50) '$.Issuer'
  );

Answer 1

I think you're looking for this. There weren't any changes needed to the JSON

select j1.LookupServiceType, j2.id, j3.AssetCode, j3.Symbol, j3.Issue, j3.Issuer
from
  OPENJSON(@json) WITH (LookupServiceType   nvarchar(4000),
                        Items NVARCHAR(MAX) '$.Items' AS JSON) j1
  outer APPLY
  OPENJSON(Items) WITH (id INT 'strict $.id',
                        value NVARCHAR(MAX) '$.value' /*AS JSON*/) j2
  outer apply 
  OPENJSON([value]) WITH (AssetCode NVARCHAR(50) '$.AssetCode',
                          Symbol NVARCHAR(50) '$.Symbol',
                          Issue NVARCHAR(50) '$.Issue',
                          Issuer NVARCHAR(50) '$.Issuer') j3;

Results

LookupServiceType   id      AssetCode   Symbol  Issue           Issuer
GetAssetValues      19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
GetAssetValues      19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
GetAssetValues      19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE

Answer 2

The issue is the format of your JSON string, you have superfluous escapes and "".

Your first query works fine once the JSON string is cleaned up:

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "LookupServiceType": "GetAssetValues",
  "Items": [
    {
      "id": "19676",
      "value": {"AssetCode":"TDAACC","Symbol":null,"Issue":"ACCOUNT #49","Issuer":"TD AMERITRADE"}
    },
    {
      "id": "19677",
      "value": {"AssetCode":"RE100","Symbol":null,"Issue":"APN: 057","Issuer":"SAN ANTONIO TX 78212"}
    },
    {
      "id": "19908",
      "value": {"AssetCode":"NALIPO","Symbol":null,"Issue":"POLICY # L0472","Issuer":"NATIONWIDE LIFE"}
    }
  ]
}';

SELECT *
FROM OPENJSON(@json, '$.Items')  
  WITH (
    Items int '$.id',
    AssetCode NVARCHAR(50) '$.value.AssetCode',
    Symbol NVARCHAR(50) '$.value.Symbol',
    Issue NVARCHAR(50) '$.value.Issue',
    Issuer NVARCHAR(50) '$.value.Issuer'
  );

Results:

Items   AssetCode   Symbol  Issue           Issuer
19676   TDAACC      NULL    ACCOUNT #49     TD AMERITRADE
19677   RE100       NULL    APN: 057        SAN ANTONIO TX 78212
19908   NALIPO      NULL    POLICY # L0472  NATIONWIDE LIFE

Alternatively if you don't wish to fix your JSON you could use the following statement:

SELECT *
FROM OPENJSON(REPLACE(REPLACE(Replace(@json, '\', ''), '"{', '{'), '}"', '}'), '$.Items')  
  WITH (
    Items int '$.id',
    value NVARCHAR(MAX) '$.value',
    AssetCode NVARCHAR(50) '$.value.AssetCode',
    Symbol NVARCHAR(50) '$.value.Symbol',
    Issue NVARCHAR(50) '$.value.Issue',
    Issuer NVARCHAR(50) '$.value.Issuer'
  );