JSON parse SQL Server

Question

Finding ways to parse child elements as rows.

How to parse this JSON and get budget options as rows and also get the other columns.

Let's say the table has columns Id, Col1, Col2, Recommendation, where Recommendation contains JSON text with values like this:

{
  "currentBudgetAmountMicros": "100000000",
  "recommendedBudgetAmountMicros": "210000000",
  "budgetOptions": [
    {
      "budgetAmountMicros": "100000000",
      "impact": {
        "baseMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        },
        "potentialMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        }
      }
    },
    {
      "budgetAmountMicros": "210000000",
      "impact": {
        "baseMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        },
        "potentialMetrics": {
          "impressions": 13824,
          "clicks": 2400,
          "costMicros": "736541993",
          "conversions": 21.51
        }
      }
    }
  ]
}

These answers and comments are helpful? https://stackoverflow.com/a/2868725/1068246 — Muzaffer Galata, Sep 20 '20 at 20:09

score 0 · Answer 1 · answered Sep 21 '20 at 02:16

Something like this

Json data

declare @json           nvarchar(max)=N'
{
  "currentBudgetAmountMicros": "100000000",
  "recommendedBudgetAmountMicros": "210000000",
  "budgetOptions": [
    {
      "budgetAmountMicros": "100000000",
      "impact": {
        "baseMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        },
        "potentialMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        }
      }
    },
    {
      "budgetAmountMicros": "210000000",
      "impact": {
        "baseMetrics": {
          "impressions": 10719,
          "clicks": 1811,
          "costMicros": "547948333",
          "conversions": 16.23
        },
        "potentialMetrics": {
          "impressions": 13824,
          "clicks": 2400,
          "costMicros": "736541993",
          "conversions": 21.51
        }
      }
    }
  ]
}';

Query

select oj.currentBudgetAmountMicros, oj.recommendedBudgetAmountMicros,
       bo.budgetAmountMicros,
       bm.impressions bm_impressions,
       bm.clicks bm_clicks,
       bm.costMicros bm_costMicros,
       bm.conversions bm_conversions,
       pm.impressions pm_impressions,
       pm.clicks pm_clicks,
       pm.costMicros pm_costMicros,
       pm.conversions pm_conversions
from openjson(@json) with (currentBudgetAmountMicros        nvarchar(4000),
                           recommendedBudgetAmountMicros    nvarchar(4000),
                           budgetOptions                    nvarchar(max) as json) oj
     cross apply
     openjson(oj.budgetOptions) with (budgetAmountMicros    nvarchar(4000),
                                      impact                nvarchar(max) as json) bo
     cross apply
     openjson(bo.impact) with (baseMetrics                  nvarchar(max) as json,
                               potentialMetrics             nvarchar(max) as json) i
     cross apply
     openjson(i.baseMetrics) with (impressions              int,
                                   clicks                   int,
                                   costMicros               nvarchar(4000),
                                   conversions              decimal(14,2)) bm
     cross apply
     openjson(i.potentialMetrics) with (impressions         int,
                                        clicks              int,
                                        costMicros          nvarchar(4000),
                                        conversions         decimal(14,2)) pm;

Output

currentBudgetAmountMicros   recommendedBudgetAmountMicros   budgetAmountMicros  bm_impressions  bm_clicks   bm_costMicros   bm_conversions  pm_impressions  pm_clicks   pm_costMicros   pm_conversions
100000000                   210000000                       100000000           10719           1811        547948333       16.23           10719           1811        547948333       16.23
100000000                   210000000                       210000000           10719           1811        547948333       16.23           13824           2400        736541993       21.51

score 0 · Answer 2 · answered Sep 21 '20 at 02:46

select 
    bop.currentBudgetAmountMicros, bop.recommendedBudgetAmountMicros,
    bam.*
from openjson(@json)
with 
(
currentBudgetAmountMicros varchar(20),
recommendedBudgetAmountMicros varchar(20),
budgetOptions nvarchar(max) as json
) as bop
cross apply openjson(bop.budgetOptions)
with
(
budgetAmountMicros varchar(20),
--basemetrics
base_impressions int '$.impact.baseMetrics.impressions',
base_clicks int '$.impact.baseMetrics.clicks',
base_costMicros varchar(20) '$.impact.baseMetrics.costMicros',
base_conversions decimal(9,2) '$.impact.baseMetrics.conversions',
--potentialmetrics
potential_impressions int '$.impact.potentialMetrics.impressions',
potential_clicks int '$.impact.potentialMetrics.clicks',
potential_costMicros varchar(20) '$.impact.potentialMetrics.costMicros',
potential_conversions decimal(9,2) '$.impact.potentialMetrics.conversions'
) as bam

JSON parse SQL Server

2 Answers2