Home > Enterprise >  How to parse two JSON in SQL Server?
How to parse two JSON in SQL Server?

Time:07-22

I'm parsing a column from a table called "respuesta_api" which itself contains a JSON.

SELECT 
    JSON_VALUE(respuesta_api, '$.RESPONSECODE') AS RESPONSECODE ,
    JSON_VALUE(respuesta_api, '$.DICTAMEN') AS DICTAMEN ,
    JSON_VALUE(respuesta_api, '$.CEDULA') AS CEDULA ,
    JSON_VALUE(respuesta_api, '$.PAIS') AS PAIS ,
    JSON_QUERY(respuesta_api, '$.REGLAS') AS REGLAS ,
    JSON_VALUE(respuesta_api, '$.VERSION') AS VERSION ,
    fecha_respuesta AS FECHA_RESPUESTA,
    id_consulta AS ID_CONSULTA
FROM 
    TABLE_ORIGEN;

Output:

RESPONSECODE DICTAMEN CEDULA PAIS REGLAS VERSION FECHA_RESPUESTA ID_CONSULTA
OK MALO 1234 EEUU {"R20000A":0,"R20000B":0,"R20000C":2,"R20101A":19,"R20101B":19,"R20201A":19,"R20201B":19,"R20102":"DOMINICANA","R20202":"4","R20103":1,"R20203":0,"R20104":0,"R20204":0,"R20105A":0,"R20105B":0,"R20205A":0,"R20205B":0,"R20106A":0,"R20106B":0,"R20106C":0,"R20206A":0,"R20206B":0,"R20206C":0,"R20107A":0,"R20107B":0,"R20107C":0,"R20207A":0,"R20207B":0,"R20207C":0,"R20108A":0,"R20108B":0,"R20208A":0,"R20208B":0,"R20109A":true,"R20109B":true,"R20109C":true,"R20109D":true,"R20209A":true,"R20209B":true,"R20209C":true,"R20209D":true,"R30100":1000} 916248E95C7CF82327773A44B2175A82 2022-06-09 2598
OK APROBADO 5678 EEUU {"R10001":0,"R10002":1,"R10003":0,"R10004":0} 7FFB3B9A1158E444FE373309BA41004C 2022-06-09 7654

But in turn that JSON which I already parses, also has another JSON called "REGLAS" (JSON_QUERY(respuesta_api, '$.REGLAS') AS REGLAS) which I need to parse into new columns like this:

RESPONSECODE DICTAMEN CEDULA PAIS R20000A R20000B R20000C R20101A R...n R10001 R10002 R10003 R10004 AVERSION FECHA_RESPUESTA ID_CONSULTA
OK MALO 1234 EEUU 0 0 2 19 n null null null null 916248E95C7CF82327773A44B2175A82 2022-06-09 2598
OK APROBADO 5678 EEUU null null null null nulln 0 1 0 0 7FFB3B9A1158E444FE373309BA41004C 2022-06-09 7654

I know it's super messy, but it's what I need.

Is it possible to do this in SQL Server?

CodePudding user response:

You are quite near to answer.

As JSON_QUERY() extracts an object or an array from a JSON string, and use JSON_VALUE to extract value from the JSON object.

JSON_VALUE(JSON_QUERY(respuesta_api, '$.REGLAS'), '$.R20000A')

While this can be simplified as:

JSON_VALUE(respuesta_api, '$.REGLAS.R20000A')

with the JSON Path expressions (Refer to the table).

Sample DB Fiddle

CodePudding user response:

Looks like this would be much easier with OPENJSON.

You can get a whole object such as REGLAS by using nvarchar(max) AS JSON, then feed it into the next OPENJSON using CROSS APPLY

SELECT
  j1.RESPONSECODE,
  j1.DICTAMEN,
  j1.CEDULA,
  j1.PAIS,
  j1.VERSION,
  j2.*,
  o.FECHA_RESPUESTA,
  o.ID_CONSULTA
FROM TABLE_ORIGEN o
CROSS APPLY OPENJSON(o.respuesta_api)
  WITH (
    RESPONSECODE varchar(10),
    DICTAMEN varchar(100),
    CEDULA int,
    PAIS varchar(100),
    REGLAS nvarchar(max) AS JSON,
    VERSION varchar(100)
  ) j1
CROSS APPLY OPENJSON(j1.REGLAS)
  WITH (
    R20000A int,
    R20000B int,
    R20000C int,
    R20101A int,
    R10001 int,
    R10002 int,
    R10003 int,
    R10004 int
  ) j2;

SQL Fiddle

  • Related