Azure SQL Database (MSSQL) -…

Azure SQL Database (MSSQL) – Working with JSON Data

SELECT

SELECT Name, Surname,
  JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
  JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' '
  + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
  JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
  AND Status = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode')

Modify

 
DECLARE @json NVARCHAR(MAX);
SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');
SELECT modifiedJson = @json;

Convert JSON collections to a rowset

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json)
  WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
  );
 

Resources & more:

  1. https://cloudblogs.microsoft.com/sqlserver/2016/01/05/json-in-sql-server-2016-part-1-of-4/
  2. https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-and-JSON-Support
  3. https://azure.microsoft.com/es-es/blog/json-support-is-generally-available-in-azure-sql-database/
  4. https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15
  5. https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver15
  6. https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver15

MySQL from version 5.7 has JSON support: https://www.sitepoint.com/use-json-data-fields-mysql-databases/

#azure #azure-database #json #mssql-json