Accessing JSON Array in SQL Server 2016 Using JSON_Value

Accessing JSON Array in SQL Server 2016 using JSON_VALUE

To get all from phoneNumbers:

DECLARE @json nvarchar(max)=
    '{
      "firstName": "John",
      "lastName" : "doe",
      "age"      : 26,
      "address"  : {
        "streetAddress": "naist street",
        "city"         : "Nara",
        "postalCode"   : "630-0192"
      },
      "phoneNumbers": [
        {
          "type"  : "iPhone",
          "number": "0123-4567-8888"
        },
        {
          "type"  : "home",
          "number": "0123-4567-8910"
        }
      ]
    }'

    SELECT [Type], [Number]
    FROM OPENJSON( @json, '$.phoneNumbers' ) 
    WITH ([Type] NVARCHAR(25) '$.type', [Number] NVARCHAR(25) '$.number');

Extracting Data from JSON Array using SQL

Here is how to get to the delinquincies.

We need to use CROSS APPLY OPENJSON(...) clause a couple of times due to the nested JSON structure.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, json_column NVARCHAR(MAX));
INSERT INTO @tbl (json_column)
VALUES
(N'{
   "creditReportDetails":[
      {
         "accountStatusSummary":{
            "accounts":[
               {
                  "amountOwed":0,
                  "amountPastDue":0,
                  "type":"Open-ended"
               },
               {
                  "amountOwed":1234,
                  "amountPastDue":0,
                  "type":"Revolving"
               },
               {
                  "amountOwed":123456,
                  "amountPastDue":0,
                  "type":"Installment"
               },
               {
                  "amountOwed":123456,
                  "amountPastDue":0,
                  "type":"Total"
               }
            ],
            "delinquincies":[
               {
                  "badDebt":null,
                  "paysAccountasAgreed":34,
                  "paysPaid120Daysor4PaymentsPastDue":null,
                  "paysPaid3060DaysorMax2PaymentsPastDue":null,
                  "paysPaid6090DaysorMax3PaymentsPastDue":null,
                  "paysPaid90120DaysorMax4PaymentsPastDue":null,
                  "repossession":null,
                  "statusNotKnown":null,
                  "type":"current"
               },
               {
                  "badDebt":null,
                  "paysAccountasAgreed":null,
                  "paysPaid120Daysor4PaymentsPastDue":null,
                  "paysPaid3060DaysorMax2PaymentsPastDue":null,
                  "paysPaid6090DaysorMax3PaymentsPastDue":null,
                  "paysPaid90120DaysorMax4PaymentsPastDue":null,
                  "repossession":null,
                  "statusNotKnown":null,
                  "type":"history"
               }
            ]
         },
         "collections":[

         ],
         "datePulled":"01/01/2020",
         "employmentHistory":[
            {
               "employer":"Some Company",
               "historyType":"Current"
            },
            {
               "employer":"Some Company",
               "historyType":"Former"
            }
         ],
         "personalInfoFromVendor":{
            "address":{
               "city":"Anywhere",
               "line1":"123 Anywhere",
               "line2":null,
               "postalZipCode":"12345",
               "territoryCode":"AB"
            },
            "dateOfBirth":"01/01/1900",
            "firstName":"ABCD",
            "lastName":"ABCD",
            "middleName":"C",
            "ssn":"1231456789",
            "suffix":null
         },
         "reportMessaging":{
            "duplicate":"No",
            "processingStatus":"Complete",
            "referenceNumber":"123456789"
         },
         "reportSummary":{
            "bankruptcies":"No",
            "collectionItems":"No",
            "expNatlRiskScore":"123",
            "publicRecords":"No"
         }
      }
   ],
   "personalCreditReportUrl":"https://abcdcreditreport.lmig.com/creditreport-details/CRS123456789012",
   "subjectId":"CRS123456789012",
   "totalCount":1,
   "CreditReportBaseResponse":{
      "lexisNexisReferenceNumber":"123456789012",
      "status":{
         "statusCode":"COMPLETE",
         "statusDescription":[
            "BALANCES ON REVOLVING ACCOUNTS",
            "BALANCE-TO-LIMIT RATIOS ON ACCOUNTS"
         ]
      },
      "subjectId":"CRS123456789012"
   }
}');
-- DDL and sample data population, end

SELECT ID
    , JSON_VALUE (delinq.[Value], '$.badDebt') as [badDebt]
    , JSON_VALUE (delinq.[Value], '$.paysAccountasAgreed') as [paysAccountasAgreed]
    -- put here everything else in between
    , JSON_VALUE (delinq.[Value], '$.type') as [type]
FROM @tbl AS tbl
    CROSS APPLY OPENJSON(tbl. json_column, 'strict $.creditReportDetails') AS cr
    CROSS APPLY OPENJSON(cr.value, 'strict $.accountStatusSummary.delinquincies') AS delinq;

Output

+----+---------+---------------------+---------+
| ID | badDebt | paysAccountasAgreed |  type   |
+----+---------+---------------------+---------+
|  1 | NULL    | 34                  | current |
|  1 | NULL    | NULL                | history |
+----+---------+---------------------+---------+

SQL Server 2016 select where in json array

You need to use OPENJSON function for reading Names array. You can use this query.

SELECT * FROM [TestTable] T
INNER JOIN [JsonTable] AS J ON T.[Name] IN (SELECT value FROM OPENJSON(J.[JsonData],'$.Names'))

Get length of json array in SQL Server 2016

Using a table instead of a variable:

SELECT count(priceLineLists.RoomTypeId)
FROM Mytable
CROSS APPLY OPENJSON (JsonDataCol, N'$.BasePriceLineList')
  WITH (
    RoomTypeId int)
      AS priceLineLists

JSON Array in SQL - Extracting multiple values from JSON array

Your statement is correct and based on the JSON data in the question, the reason for this "... JSON text is not properly formatted. Unexpected character 'h' is found at position 0' error ... " error is somewhere else.

Your JSON text is a valid JSON array, so you have two possible approaches to get your expected results:

• if this JSON array has always two items, you should use JSON_VALUE() to access each item
• if the count of the items is not known, you should use OPENJSON() with additional CROSS APPLY operator and string aggregation function.

Table:

CREATE TABLE #Data (
   [gca] nvarchar(max)
)
INSERT INTO #Data
   ([gca])
VALUES
   (N'[{"i":"https://some.image.URL 1","u":"https://some.product.url","n":"Product 1 Name","q":"1","sk":"sku number 1","st":"$499.99"},{"i":"https://some.image.URL 2","u":"https://some.product.url","n":"Product 2 Name","q":"1","sk":"sku number 2","st":"$499.99"}]'),
   (N'[{"i":"https://some.image.URL 1","u":"https://some.product.url","n":"Product 1 Name","q":"1","sk":"sku number 1","st":"$499.99"},{"i":"https://some.image.URL 2","u":"https://some.product.url","n":"Product 2 Name","q":"1","sk":"sku number 2","st":"$499.99"}]')

Statements:

-- For fixed structure with two items
SELECT JSON_VALUE ([gca], '$[0].i') + ', ' + JSON_VALUE ([gca], '$[1].i') AS [http]
FROM #Data

-- For JSON array with multiple items and SQL Server 2017+ 
SELECT j.*
FROM #Data d
CROSS APPLY (
   SELECT STRING_AGG([http], ',') AS [http]
   FROM OPENJSON(d.[gca]) WITH ([http] varchar(max) '$.i') 
) j

-- For JSON array with multiple items
SELECT STUFF(j.[http], 1, 1, N'') AS [http]
FROM #Data d
CROSS APPLY (
   SELECT CONCAT(',', [http])
   FROM OPENJSON(d.[gca]) WITH ([http] varchar(max) '$.i')
   FOR XML PATH('')
) j([http])

Output:

-------------------------------------------------
http
-------------------------------------------------
https://some.image.URL 1,https://some.image.URL 2
https://some.image.URL 1,https://some.image.URL 2

Note, that JSON support was intoduced in SQL Server 2016 and STRING_AGG() was introduced in SQL Server 2017. For string aggregation in earlier versions use FOR XML PATH.

How to get data from json column in mssql

From SQL Server 2016, you can query on JSON column. See the documentation : Work with JSON data

The interesting part for you it's Analyze JSON data with SQL queries.

This done :

select Id, PostalCode
from Address
CROSS APPLY OPENJSON (Address.Data, N'$.Addresses')
WITH (
    TypeCode varchar(50) '$.TypeCode',
    PostalCode varchar(50) '$.PostalCode'
) AS AddressesJsonData
WHERE TypeCode  = N'PERMANENT'

---

Related Topics