Crear Json desde SQLServer queries

El uso de Json se ha hecho muy popular y común para transmitir datos por medio de la web y diferentes sistemas internos y externos de diferentes empresas. Ahora a partir de SQLServer 2016 podemos crear queries que pueden generar resultados con Json.

Con el uso de Json se puede manipular el tipo de resultado que vamos a crear. Vamos a ver unos ejemplos de como se puede logar esto.

Primero vamos a crear una tabla para poder crear los queries.

Las tablas y los datos los encontramos en este link https://www.progress.com/tutorials/ado-net/code-examples-sql-server

Con el siguiente ejemplo podemos ver como generar el resultado de Json. Usando “FOR JSON PATH” y también podemos controlar el formato.

SELECT * FROM emp FOR JSON PATH

[{"empno":1,"ename":"JOHNSON","job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"empno":2,"ename":"HARDING","job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"empno":3,"ename":"TAFT","job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"empno":4,"ename":"HOOVER","job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"empno":5,"ename":"LINCOLN","job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"empno":6,"ename":"GARFIELD","job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"empno":7,"ename":"POLK","job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"empno":8,"ename":"GRANT","job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"empno":9,"ename":"JACKSON","job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"empno":10,"ename":"FILLMORE","job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"empno":11,"ename":"ADAMS","job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"empno":12,"ename":"WASHINGTON","job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"empno":13,"ename":"MONROE","job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"empno":14,"ename":"ROOSEVELT","job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]

Con el atributo de ROOT se puede crear un nodo principal para agregar todo el resto del JSON

SELECT empno,ename,job,mgr,hiredate,sal,comm,dept FROM emp

FOR JSON PATH, ROOT('emp')

{"emp":[{"empno":1,"ename":"JOHNSON","job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"empno":2,"ename":"HARDING","job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"empno":3,"ename":"TAFT","job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"empno":4,"ename":"HOOVER","job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"empno":5,"ename":"LINCOLN","job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"empno":6,"ename":"GARFIELD","job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"empno":7,"ename":"POLK","job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"empno":8,"ename":"GRANT","job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"empno":9,"ename":"JACKSON","job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"empno":10,"ename":"FILLMORE","job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"empno":11,"ename":"ADAMS","job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"empno":12,"ename":"WASHINGTON","job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"empno":13,"ename":"MONROE","job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"empno":14,"ename":"ROOSEVELT","job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]}

Podemos agrupar o crear Json dentro del Json principal como podemos ver en el siguiente ejemplo.

SELECT empno [emp.empno],ename [emp.ename],job,mgr,hiredate,sal,comm,dept FROM emp

FOR JSON PATH, ROOT('emp')

{"emp":[{"emp":{"empno":1,"ename":"JOHNSON"},"job":"ADMIN","mgr":6,"hiredate":"1990-12-17T00:00:00","sal":18000.00,"dept":4},{"emp":{"empno":2,"ename":"HARDING"},"job":"MANAGER","mgr":9,"hiredate":"1998-02-02T00:00:00","sal":52000.00,"comm":300.00,"dept":3},{"emp":{"empno":3,"ename":"TAFT"},"job":"SALES I","mgr":2,"hiredate":"1996-01-02T00:00:00","sal":25000.00,"comm":500.00,"dept":3},{"emp":{"empno":4,"ename":"HOOVER"},"job":"SALES I","mgr":2,"hiredate":"1990-04-02T00:00:00","sal":27000.00,"dept":3},{"emp":{"empno":5,"ename":"LINCOLN"},"job":"TECH","mgr":6,"hiredate":"1994-06-23T00:00:00","sal":22500.00,"comm":1400.00,"dept":4},{"emp":{"empno":6,"ename":"GARFIELD"},"job":"MANAGER","mgr":9,"hiredate":"1993-05-01T00:00:00","sal":54000.00,"dept":4},{"emp":{"empno":7,"ename":"POLK"},"job":"TECH","mgr":6,"hiredate":"1997-09-22T00:00:00","sal":25000.00,"dept":4},{"emp":{"empno":8,"ename":"GRANT"},"job":"ENGINEER","mgr":10,"hiredate":"1997-03-30T00:00:00","sal":32000.00,"dept":2},{"emp":{"empno":9,"ename":"JACKSON"},"job":"CEO","hiredate":"1990-01-01T00:00:00","sal":75000.00,"dept":4},{"emp":{"empno":10,"ename":"FILLMORE"},"job":"MANAGER","mgr":9,"hiredate":"1994-08-09T00:00:00","sal":56000.00,"dept":2},{"emp":{"empno":11,"ename":"ADAMS"},"job":"ENGINEER","mgr":10,"hiredate":"1996-03-15T00:00:00","sal":34000.00,"dept":2},{"emp":{"empno":12,"ename":"WASHINGTON"},"job":"ADMIN","mgr":6,"hiredate":"1998-04-16T00:00:00","sal":18000.00,"dept":4},{"emp":{"empno":13,"ename":"MONROE"},"job":"ENGINEER","mgr":10,"hiredate":"2000-12-03T00:00:00","sal":30000.00,"dept":2},{"emp":{"empno":14,"ename":"ROOSEVELT"},"job":"CPA","mgr":9,"hiredate":"1995-10-12T00:00:00","sal":35000.00,"dept":1}]}

De esta manera podemos generar documentos o resultados de Json con nuestros datos de SQLServer