Mssql JSON Fonksiyonları
Mssql 07 Mayıs, 2020

SQL Server 2016 da gelen JSON desteğinden sonra mssql de JSON verilerle çalışmak oldukça kolaylaştı. Bu yazıda işleri oldukça kolaylaştıran JSON fonksiyonlarından ve kullanımlarını örneklerle göstereceğim.

ISJSON (expression) : Parametre olarak verilen değerin geçerli biz JSON verisi olup olmadığını kontrol eder. Geçerliyse 1 değilse 0, parametre null ise null döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(500) = 
N'
{
  "Name": "Jack",
  "Surname": "Sparrow"
}
'
SELECT Result = ISJSON(@lcJson)

Sorgu sonucu geçerli bir JSON olduğu için 1 dir.

JSON_MODIFY (Expression,Path,NewValue): JSON dizesindeki bir özelliğin değerini günceller ve güncellenmiş JSON dizesini döndürür. Expression, güncellenecek JSON verisidir. Path, güncellenecek özelliği belirten bir JSON yolu ifadesidir ve zorunlu değildir. NewValue ise güncellemek istediğiniz yeni değerdir. Güncellenecek JSON geçerli değilse hata döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(500) = 
N'
{
  "Name": "Jack",
  "Surname": "Sparrow"
}
'

SELECT Result = JSON_MODIFY(@lcJson,'$.Name','Elizabeth')

Aşağıdaki görüldüğü gibi Name verisi Elizabeth olarak güncellendi. 

JSON_VALUE (expression,path): JSON verisinden skaler değer ayıklar. Geçerli bir JSON verimezse hata döndürür. Nvarchar(4000) türünde tek bir metin değeri döndürür. 4000 karakterden büyük skaler değerleri döndürmeniz gerekiyorsa, JSON_VALUE yerine OPENJSON kullanılmalıdır. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(MAX) =
N'
{
  "info": {
    "type": 1,
    "address": {
      "town": "Bristol",
      "county": "Avon",
      "country": "England"
    },
    "tags": [
      "Sport",
      "Water polo"
    ]
  },
  "type": "Basic"
}
'  

SELECT 
[Info.Type] = JSON_VALUE(@lcJson,'$.info.type'),
[Info.Address.Town] = JSON_VALUE(@lcJson,'$.info.address.town'),
[Info.Address.County] = JSON_VALUE(@lcJson,'$.info.address.county'),
[Info.Address.Country] = JSON_VALUE(@lcJson,'$.info.address.country'),
[Tags.0] = JSON_VALUE(@lcJson,'$.info.tags[0]'),
[Tags.1] = JSON_VALUE(@lcJson,'$.info.tags[1]'),
[Type] = JSON_VALUE(@lcJson,'$.type')

Parametre olarak verilen JSON verisi aşağıdaki gibi ayıklandı.

JSON_QUERY (expression [ , path ]): JSON verisinden bir nesneyi veya dizeyi ayıklar. Geçerli bir JSON verimezse hata döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(MAX) =
N'
{
  "info": {
    "type": 1,
    "address": {
      "town": "Bristol",
      "county": "Avon",
      "country": "England"
    },
    "tags": [
      "Sport",
      "Water polo"
    ]
  },
  "type": "Basic"
}
'  

SELECT 
[Info] = JSON_QUERY(@lcJson,'$.info'),
[Info.Address] = JSON_QUERY(@lcJson,'$.info.address'),
[Tags] = JSON_QUERY(@lcJson,'$.info.tags'),
[Type] = JSON_QUERY(@lcJson,'$.type')

Aşağıda görüldüğü gibi JSON verisinden belirli bir dizeyi veya nesneyi ayıklar. Aşağıdaki Type verisi skaler bir değer olduğu için null olarak alındı.

OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]: JSON metnini ayrıştırır, nesneleri ve özellikleri satır ve sütun olarak döndüren tablo değerli bir işlevdir. Başka bir deyişle bir JSON verisi üzerinde bir satır kümesi görünümü sağlar. Belirtilen jsonExpression verisinde JSON metnini arar ve yalnızca istenilen parçayı ayrıştırır. WITH komutuyla beraber kullanıldığı gibi tek başına veya CROSS APPLY komutuyla birlikte de kullanılabilir. Örnek kullanımı:

DECLARE @lcJson nvarchar(1000) =
N'
[
  {
    "Id": 1,
    "User": {
      "Name": "John"
    },
    "Hobbies": [
      {
        "Name": "Reading"
      },
      {
        "Name": "Surfing"
      }
    ]
  }
]
'

SELECT 
    [Id] = Person.Id, 
	[User] = Person.UserName, 
	[Hobby] = Hobby.Name
FROM OPENJSON (@lcJson)
WITH
(
    Id INT '$.Id',
    UserName NVARCHAR(100) '$.User.Name',
    Hobbies NVARCHAR(max) '$.Hobbies' AS JSON
) 
AS Person
CROSS APPLY OPENJSON(Hobbies)
WITH
(
    Name NVARCHAR(100) '$.Name'
) AS Hobby

Bu sorgunun çıktısı aşağıdaki gibi bir sonuç kümesidir:

Referanslar:

Kodlamaya devam! 


Yorumlar

Mssql JSON Fonksiyonları
Mssql 07 Mayıs, 2020

SQL Server 2016 da gelen JSON desteğinden sonra mssql de JSON verilerle çalışmak oldukça kolaylaştı. Bu yazıda işleri oldukça kolaylaştıran JSON fonksiyonlarından ve kullanımlarını örneklerle göstereceğim.

ISJSON (expression) : Parametre olarak verilen değerin geçerli biz JSON verisi olup olmadığını kontrol eder. Geçerliyse 1 değilse 0, parametre null ise null döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(500) = 
N'
{
  "Name": "Jack",
  "Surname": "Sparrow"
}
'
SELECT Result = ISJSON(@lcJson)

Sorgu sonucu geçerli bir JSON olduğu için 1 dir.

JSON_MODIFY (Expression,Path,NewValue): JSON dizesindeki bir özelliğin değerini günceller ve güncellenmiş JSON dizesini döndürür. Expression, güncellenecek JSON verisidir. Path, güncellenecek özelliği belirten bir JSON yolu ifadesidir ve zorunlu değildir. NewValue ise güncellemek istediğiniz yeni değerdir. Güncellenecek JSON geçerli değilse hata döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(500) = 
N'
{
  "Name": "Jack",
  "Surname": "Sparrow"
}
'

SELECT Result = JSON_MODIFY(@lcJson,'$.Name','Elizabeth')

Aşağıdaki görüldüğü gibi Name verisi Elizabeth olarak güncellendi. 

JSON_VALUE (expression,path): JSON verisinden skaler değer ayıklar. Geçerli bir JSON verimezse hata döndürür. Nvarchar(4000) türünde tek bir metin değeri döndürür. 4000 karakterden büyük skaler değerleri döndürmeniz gerekiyorsa, JSON_VALUE yerine OPENJSON kullanılmalıdır. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(MAX) =
N'
{
  "info": {
    "type": 1,
    "address": {
      "town": "Bristol",
      "county": "Avon",
      "country": "England"
    },
    "tags": [
      "Sport",
      "Water polo"
    ]
  },
  "type": "Basic"
}
'  

SELECT 
[Info.Type] = JSON_VALUE(@lcJson,'$.info.type'),
[Info.Address.Town] = JSON_VALUE(@lcJson,'$.info.address.town'),
[Info.Address.County] = JSON_VALUE(@lcJson,'$.info.address.county'),
[Info.Address.Country] = JSON_VALUE(@lcJson,'$.info.address.country'),
[Tags.0] = JSON_VALUE(@lcJson,'$.info.tags[0]'),
[Tags.1] = JSON_VALUE(@lcJson,'$.info.tags[1]'),
[Type] = JSON_VALUE(@lcJson,'$.type')

Parametre olarak verilen JSON verisi aşağıdaki gibi ayıklandı.

JSON_QUERY (expression [ , path ]): JSON verisinden bir nesneyi veya dizeyi ayıklar. Geçerli bir JSON verimezse hata döndürür. Örnek kullanımı:

DECLARE @lcJson NVARCHAR(MAX) =
N'
{
  "info": {
    "type": 1,
    "address": {
      "town": "Bristol",
      "county": "Avon",
      "country": "England"
    },
    "tags": [
      "Sport",
      "Water polo"
    ]
  },
  "type": "Basic"
}
'  

SELECT 
[Info] = JSON_QUERY(@lcJson,'$.info'),
[Info.Address] = JSON_QUERY(@lcJson,'$.info.address'),
[Tags] = JSON_QUERY(@lcJson,'$.info.tags'),
[Type] = JSON_QUERY(@lcJson,'$.type')

Aşağıda görüldüğü gibi JSON verisinden belirli bir dizeyi veya nesneyi ayıklar. Aşağıdaki Type verisi skaler bir değer olduğu için null olarak alındı.

OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]: JSON metnini ayrıştırır, nesneleri ve özellikleri satır ve sütun olarak döndüren tablo değerli bir işlevdir. Başka bir deyişle bir JSON verisi üzerinde bir satır kümesi görünümü sağlar. Belirtilen jsonExpression verisinde JSON metnini arar ve yalnızca istenilen parçayı ayrıştırır. WITH komutuyla beraber kullanıldığı gibi tek başına veya CROSS APPLY komutuyla birlikte de kullanılabilir. Örnek kullanımı:

DECLARE @lcJson nvarchar(1000) =
N'
[
  {
    "Id": 1,
    "User": {
      "Name": "John"
    },
    "Hobbies": [
      {
        "Name": "Reading"
      },
      {
        "Name": "Surfing"
      }
    ]
  }
]
'

SELECT 
    [Id] = Person.Id, 
	[User] = Person.UserName, 
	[Hobby] = Hobby.Name
FROM OPENJSON (@lcJson)
WITH
(
    Id INT '$.Id',
    UserName NVARCHAR(100) '$.User.Name',
    Hobbies NVARCHAR(max) '$.Hobbies' AS JSON
) 
AS Person
CROSS APPLY OPENJSON(Hobbies)
WITH
(
    Name NVARCHAR(100) '$.Name'
) AS Hobby

Bu sorgunun çıktısı aşağıdaki gibi bir sonuç kümesidir:

Referanslar:

Kodlamaya devam! 


Benzer Yazılar

mssql-json-kullanimi

Mssql JSON Kullanımı

03 Mayıs, 2020

mssql-merge-kullanimi

Mssql Merge Kullanımı

14 Mayıs, 2020

Yorumlar