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'
);