How to read JSON text from a column in a Sql Server 2016 table

Question

I have JSon data on an Sql server.

{
 "PartnerName": null,
 "PartnerCurrencyCode": "UAD",
 "PricingConditions": [
  {
    "PricingConditionId": 659853,
    "ConditionTypeCode": "ABCD",
    "ConcessionItemTypeCode": "ABC",
    "PriceLevel": null
  }
],
"CurrencyMultiplier": 0
}

Expected results:

PartnerName  PartnerCode    PartnerCurrencyCode  PricingConditionId
NULL         NULL           UAD                   659853

I read that Sql Server 2016 has the ability to read JSON data and handle the following requests.

Request 1:

SELECT * FROM OPENJSON(@json, '$')

Request 2:

SELECT *  
FROM OPENJSON(@json)  
WITH (PartnerName NVARCHAR(50) '$.PartnerName',
     PartnerCode nvarchar(50) '$.PartnerCode',
     PartnerCurrencyCode nvarchar(50) '$.PartnerCurrencyCode',
     PricingConditionId nvarchar(50)   '$.PricingConditions.PricingConditionId')

Query Results 2:

PartnerName  PartnerCode    PartnerCurrencyCode  PricingConditionId
NULL         NULL           UAD                  NULL

I expected the value of "PricingConditionId" - 659853

Answer 1

Try this query:

SELECT PartnerName,
       PartnerCode,
       PartnerCurrencyCode,
       [PricingConditions[0]].PricingConditionId AS ProductID1
FROM OPENJSON (@json) 
WITH (
    PartnerName NVARCHAR(50),
    PartnerCode NVARCHAR(50),
    PartnerCurrencyCode NVARCHAR(50),
    [PricingConditions[0]].PricingConditionId NVARCHAR(50)
) AS Partners

Here is a link to an excellent article that discusses several approaches to solving your problem.