SQL Server y JSON

Una de las maneras más simples de retornar un set de datos en un objeto JSON es por medio de las sentencias FOR JSON AUTO y FOR JSON PATH. Al utilizar una de estas sentencias el resultado del query no será retornado de la manera tradicional, filas y columnas, sino que retornará un objeto tipo JSON.

Ejemplo:

SELECT TOP (2) [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
FROM [AdventureWorks2016].[HumanResources].[Employee]
FOR JSON AUTO

SELECT TOP (2) [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
FROM [AdventureWorks2016].[HumanResources].[Employee]
FOR JSON PATH



[{"BusinessEntityID":1,"NationalIDNumber":"295847284","LoginID":"adventure-works\\ken0"},{"BusinessEntityID":2,"NationalIDNumber":"245797967","LoginID":"adventure-works\\terri0","OrganizationNode":"\/1\/"}]

Importante recordar que los tipos de datos soportados en los objetos tipo JSON son: null, string, number y boolean. Esto es importante mencionarlo debido a la conversión que se realiza en los diferentes tipos de datos de Sql Server:
  • char, nchar, varchar, nvarchar, date, time, datetime, datetime2, datetimeoffset, money, uniqueidentifier => String.
  • Tinyint, int, smallint, bigint, real, float, decimal, numeric => Numeric
  • Bit => Boolean

Ambas sentencias FOR JSON AUTO y FOR JSON PATH permiten especificar tres opciones adicionales:
  1. Root: Adiciona un miembro en la raíz del objeto.
  2. Include_null_values: Incluye los valores nulos en el set de datos del resultado. Se debe especificar esta opción ya que por defecto el set de datos no muestra los valores nulos.
  3. Without_array_wrapper: Elimina los corchetes ([]) que envuelven al set de datos de retorno. Esta opción es excluyente con la opción Root.

La misma consulta anterior pero con la opción Without_array_wrapper:

SELECT TOP (2) [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
FROM [AdventureWorks2016].[HumanResources].[Employee]
FOR JSON AUTO,
WITHOUT_ARRAY_WRAPPER

{"BusinessEntityID":1,"NationalIDNumber":"295847284","LoginID":"adventure-works\\ken0"},{"BusinessEntityID":2,"NationalIDNumber":"245797967","LoginID":"adventure-works\\terri0","OrganizationNode":"\/1\/"}

Ejemplo utilizando la opción Root:

SELECT TOP (2) [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
FROM [AdventureWorks2016].[HumanResources].[Employee]
FOR JSON AUTO,
ROOT('Empleado')

{"Empleado":[{"BusinessEntityID":1,"NationalIDNumber":"295847284","LoginID":"adventure-works\\ken0"},{"BusinessEntityID":2,"NationalIDNumber":"245797967","LoginID":"adventure-works\\terri0","OrganizationNode":"\/1\/"}]}

Sql Server también ofrece la posibilidad de leer un objeto JSON y presentarlo de manera tracional, filas y columnas, para esto se puede utiliza la función OPENJSON. Esta función recibe dos parámetros: el primero es el objeto JSON y el segundo que es opcional, la ruta o el punto en el cuál comenzar el procesamiento del objeto JSON. Si el segundo parámetro no es especifica todo el objeto JSON será procesado.

El set de datos retornado, tendrá un esquema por defecto, este esquema se puede definir utilizando la sentencia WITH.

Ejemplo sin definir un esquema explícitamente:

DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null}
}';
SELECT *
FROM OPENJSON(@json);



Al no especificar un esquema explícitamente, SQL Server retorna 3 columnas, estas son:
  1. Key: Nombre de la entidad del objeto JSON
  2. Value: El valor del objeto JSON
  3. Type: Un valor de tipo de dato tiny integer. Este tiene 6 posibles valores:
    • 0: Null
    • 1: String
    • 2: Number
    • 3: Boolean (true or false)
    • 4: Array
    • 5: Object

La función OPENJSON convierte en tabla el primer nivel del objeto cómo tal. Para retornar las propiedades anidadas del objeto es necesario especificar el segundo parámetro. Ejemplo:



DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null}
}';
SELECT *
FROM OPENJSON(@json, '$.Persona');

El resultado de la consulta es:



Para cuándo el objeto JSON contiene propiedades anidadas, estas serán retornadas cómo un objeto JSON en el set de resultado. Ejemplo:

DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null,
"Estudios": {
"Titulo":"Bachillerato",
"Anio":"2000"}}
}';
SELECT *
FROM OPENJSON(@json, '$.Persona');



Si se desea presentar de manera tradicional los datos de la entidad anidada, esta se debe especificar en la consulta. Ejemplo:

DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null,
"Estudios": {
"Titulo":"Bachillerato",
"Anio":"2000"}}
}';
SELECT *
FROM OPENJSON(@json, '$.Persona.Estudios');





Y si se quiere mostrar en una misma tabla la combinación de ambas entidades, se puede realizar utilizando la función JSON_VALUE(). Ejemplo:

DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":[{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null,
"Estudios": [{
"Titulo":"Bachillerato",
"Anio":"2000"}]}
]}';
SELECT
JSON_VALUE(P.Value, '$.Identificacion') AS Identificacion,
JSON_VALUE(P.Value, '$.Nombre') AS Nombre,
JSON_VALUE(E.Value, '$.Titulo') AS Titulo
FROM OPENJSON(@json, '$.Persona') AS P
CROSS APPLY OPENJSON(P.value, '$.Estudios') as E



Definiendo un esquema
Para definir un esquema se debe especificar la sentencia WITH. Recordar que si no se especifica esta sentencia, SQL Server define un esquema por defecto.

DECLARE @json AS NVARCHAR(MAX) = N'
{
"Persona":{
"Identificacion":1,
"Nombre":"Marcos",
"Apellido": "Perez Perez",
"Fecha Nacimiento":"1976-10-03",
"Edad":null,
"Estudios": {
"Titulo":"Bachillerato",
"Anio":"2000"}}
}';

SELECT *
FROM OPENJSON(@json)
WITH
(
Identificacion NVARCHAR(50) '$.Persona.Identificacion',
Nombre NVARCHAR(100) '$.Persona.Nombre',
Apellido VARCHAR(100) '$.Persona.Apellido'
);


El resultado de la consulta es:




Comentarios

Publicar un comentario

Entradas populares de este blog

Clases POCO y Clases DTO

Clases POCO y Clases DTO A continuación haré una breve descripción, diferencias y la utilidad de las clases POCO y DTO . POCO , es un termino analogo de POJO, dónde la “J” quiere decir JAVA, termino fue introducido por Martin Fowler. Las clases POCO (Plain Old CLR Object), son clases simples dónde únicamente se define el domino a mantener, esta clase contiene propiedades y métodos. Estas clases no conocen de frameworks externos, infraestructura alguna, persistencias, interfaces ni herencias. Ejemplo de una clase POCO: public class Carro { public string CarroId { get ; set ; } public int Model { get ; set ; } public string Marca { get ; set ; } public string Color { get ; set ; } public long PersonaId { get ; set ; } public Carro( string carroid, int model, string marca, string color, long personaId) { this .CarroId = carroid; this...
Leer más

INTELLITEST

Imagen
Es una herramienta que sirve para realizar y registrar el historial de historial de ejecución del código que se ejecuta al lanzar una acción o un evento dentro de la aplicación. Esto permite realizar una depuración con mayor detalle y en menor tiempo cuando hay algún defecto en el código. Al identificar un punto de falla y registrar los eventos que se ejecutaron antes de este, esto puede ayudar al desarrollador a establecer el breakpoint y ahondar en la falla con más detalle. La edición Enterprise es en la que se puede utilizar esta herramienta. Se puede utilizar en aplicaciones basadas en el framework 2.0, tanto para el lenguaje VB y C#. Desde aplicaciones ASP.NET, Azure, WCF, WPF o Sharepoint; para ASP.NET CORE hay limitaciones.   Intellitest no solo ayuda en la etapa de desarrollo, también permite recolectar información en etapas de producción. Estos datos generados en un ambiente productivo se recopilan y se guardar en un archivo de extensión (.iTrace). Este arch...
Leer más

INTERFACES C#

INTERFACES C# En la documentación de Microsoft se encuentra esta definición: “Las interfaces escriben un grupo de funcionalidades relacionadas que pueden pertenecer a una clase o estructura.” Las interfaces definen sin implementar una serie de propiedades, métodos, eventos o indexadores, esto define una especie de contrato que será implementado por una clase o estructura. Importante especificar que todos las definiciones especificadas en la interfaz deben ser públicas ya que deben ser accesibles por todos los tipos que la requieren en su especificación. El uso de interfaces, puede permitir que el código sea mantenible, testeable y extensible. Importante recordar que uno de los principios de desarrollo del acrónimo SOLID tiene que ver con el manejo de las interfaces. Aunque no quiere decir que todo debe tener y ser manejado desde las interfaces. Las interfaces se definen con la palabra reservada interface, ejemplo: public interface INombre{} Como recomendación, e...
Leer más