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:

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:

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