Databases Konsultan IT Bandung Suhendra Yohana Putra - Konsultan IT Bandung - Suhendra Yohana Putra

Convert JSON String To Table SQL Server 2008R2

suhendrayputra 17/12/2021 SQL Server Leave a comment 478 Views

Sebelum memulai coding, mumpung lagi di Jakarta dan dengan dengan Cafe sekitar Kemayoran kita bisa sambil menikmati Kopi dan Snack terlebih dahulu, selagi menunggu Mobil di service di bengkel Honda Harapan Indah kita coba untuk membuat 1 buah Script dimana fungsinya adalah mengkonversi sebuah Json String agar bisa diolah datanya. Tetapi kali ini bukan dari sisi aplikasi melainkan menggunakan PL/SQL yang tentunya pengolahan JSONString ini dilakukan didalam DATABASE.

JSON

JSON adalah turunan JavaScript yang digunakan dalam transfer dan penyimpanan data. Kekinian, bahasa ini sering dimanfaatkan dalam pembuatan aplikasi web.

Fungsi JSON kurang lebih sama dengan XML. Sebelum JSON diciptakan, format bahasa markup ini sering digunakan dengan JavaScript dalam AJAX (asynchronous JavaScript and XML) untuk penyimpanan dan perpindahan data.

Berkat gabungan keduanya, developer dapat membuat halaman website di mana informasinya dapat diperbarui tanpa harus memuat ulang halaman tersebut. Ini sudah menjadi hal yang umum, terutama dengan menjamurnya tren aplikasi web.

Nah, JSON memiliki fungsi yang sama. Bedanya, ia memiliki beberapa keunggulan yang membuatnya lebih sering digunakan daripada XML, termasuk:

  1. File yang lebih ringan
  2. Struktur kode yang lebih sederhana
  3. Kode yang mudah dipahami oleh manusia

Agar Anda paham dengan ketiga keunggulan tersebut, mari simak contoh yang ada di poin berikutnya.

Perbandingan JSON dan XML

Seberapa sederhanakah kode JSON jika dibandingkan dengan kode XML? Untuk mengetahuinya, Anda dapat memperhatikan kedua contoh di bawah ini. Contoh pertama adalah kode XML.

<users>
    <user>
        <username>Anton</username> <lokasi>Bandung</lokasi>
    </user>
    <user>
        <username>Budi</username> <lokasi>Semarang</lokasi>
    </user>
    <user>
        <username>Nana</username> <lokasi>Surabaya</lokasi>
    </user>
    <user>
        <username>Jamal</username> <lokasi>Tangerang</lokasi>
    </user>
</users>

Nah, berikut ini adalah contoh kode JSON.

{"users": [
{"username" : "Anton", "lokasi" : "Bandung"},
{"username" : "Budi", "lokasi" : "Semarang"},
{"username" : "Nana", "lokasi" : "Surabaya"},
{"username" : "Jamal", "lokasi" : "Tangerang"}
] }

Dapat Anda lihat bahwa kode JSON lebih ringkas karena tidak memerlukan tag pembuka dan penutup. Dengan demikian, kodenya juga lebih mudah dipahami. Di samping itu, kelebihan ini juga memberikan dua manfaat lainnya, yaitu:

  1. Proses loading data yang lebih ringan karena ukuran file yang kecil.
  2. Penulisan kode yang lebih cepat dengan kode yang sederhana.

Database SQL Server

Sebelum saya ulas pengertian Microsoft SQL Server, saya akan coba pecah terlebih dahulu.

  1. SQL adalah singkatan atau kependekan dari Structured Query Language.
  2. SQL SERVER adalah sistem manajemen database relasional (RDBMS) yang dirancang untuk aplikasi dengan arsitektur client/server.

Istilah client, server, dan client/server dapat digunakan untuk merujuk kepada konsep yang sangat umum atau hal yang spesifik dari perangkat keras atau perangkat lunak. Pada level yang sangat umum. Jadi secara umum SQL Server adalah sebuah Software yang dibuat oleh perusahaan Microsoft yang digunakan untuk membuat database yang dapat diimplementasikan untuk Client Server.

  1. CLIENT adalah setiap komponen dari sebuah sistem yang meminta layanan atau sumber daya (resource) dari komponen sistem lainnya.
  2. SERVER adalah setiap komponen sistem yang menyediakan layanan atau sumber daya ke komponen sistem lainnya.

Apakah SQL Server bisa memanipulasi JSON ? Bisa. Pada SQL Server 2008r2, dapat dilakukan dengan cara membuat sebuah fungsi untuk memecah json string kedalam sebuah table. Sebetulnya syntax yang asli berasal dari situs web ini. https://en.dirceuresende.com/blog/lendo-strings-json-convertendo-tabelas-para-json-e-para-xml-no-sql-server/ , tetapi saya rubah ke bahasa yang lebih universal (Inggris).

Untuk penggunaannya sendiri sebagai berikut:

Buat Database pada SQL Server.

Untuk membuat database pada SQL Server bisa menggunakan script dibawah

USE master
GO

CREATE DATABASE DBTEST
ON PRIMARY (
NAME = N'DBTEST',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBTEST.mdf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
)
LOG ON (
NAME = N'DBTEST_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBTEST_log.ldf',
SIZE = 1024 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10 %
)
GO

ALTER DATABASE DBTEST
SET
ANSI_NULL_DEFAULT OFF,
ANSI_NULLS OFF,
ANSI_PADDING OFF,
ANSI_WARNINGS OFF,
ARITHABORT OFF,
AUTO_CLOSE OFF,
AUTO_CREATE_STATISTICS ON,
AUTO_SHRINK OFF,
AUTO_UPDATE_STATISTICS ON,
AUTO_UPDATE_STATISTICS_ASYNC OFF,
COMPATIBILITY_LEVEL = 100,
CONCAT_NULL_YIELDS_NULL OFF,
CURSOR_CLOSE_ON_COMMIT OFF,
CURSOR_DEFAULT GLOBAL,
DATE_CORRELATION_OPTIMIZATION OFF,
DB_CHAINING OFF,
HONOR_BROKER_PRIORITY OFF,
MULTI_USER,
NUMERIC_ROUNDABORT OFF,
PAGE_VERIFY CHECKSUM,
PARAMETERIZATION SIMPLE,
QUOTED_IDENTIFIER OFF,
READ_COMMITTED_SNAPSHOT OFF,
RECOVERY FULL,
RECURSIVE_TRIGGERS OFF,
TRUSTWORTHY OFF
WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE DBTEST
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

ALTER DATABASE DBTEST
SET DISABLE_BROKER
GO

ALTER DATABASE DBTEST
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER AUTHORIZATION ON DATABASE::DBTEST TO sa
GO

 

Membuat Fungsi Convert JSONReader

CREATE FUNCTION dbo.fnJSONRead (@JSON NVARCHAR(MAX))
RETURNS @Retorno TABLE (
  Id_Element INT NULL
 ,No_Sequence [INT] NULL
 ,Id_Object_Parent INT
 ,Id_Object INT
 ,Ds_Name NVARCHAR(2000)
 ,Ds_String NVARCHAR(MAX) NOT NULL
 ,Ds_Type VARCHAR(10) NOT NULL
)
AS
BEGIN
  DECLARE @FirstObject INT
         ,@OpenDelimiter INT
         ,@NextOpenDelimiter INT
         ,@NextCloseDelimiter INT
         ,@Type NVARCHAR(10)
         ,@NextCloseDelimiterChar CHAR(1)
         ,@Contents NVARCHAR(MAX)
         ,@Start INT
         ,@end INT
         ,@param INT
         ,@EndOfDs_Name INT
         ,@token NVARCHAR(200)
         ,@value NVARCHAR(MAX)
         ,@No_Sequence INT
         ,@Ds_Name NVARCHAR(200)
         ,@Id_Object_Parent INT
         ,@lenJSON INT
         ,@characters NCHAR(36)
         ,@result BIGINT
         ,@index SMALLINT
         ,@Escape INT
  DECLARE @Strings TABLE (
    String_ID INT IDENTITY (1, 1)
   ,Ds_String NVARCHAR(MAX)
  )
  SELECT
    @characters = '0123456789abcdefghijklmnopqrstuvwxyz'
   ,@No_Sequence = 0
   ,@Id_Object_Parent = 0;
  WHILE (1 = 1)
  BEGIN
  SELECT
    @Start = PATINDEX('%[^a-zA-Z]["]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN);
  IF (@Start = 0)
    BREAK
  IF (SUBSTRING(@JSON, @Start + 1, 1) = '"')
  BEGIN
    SET @Start = @Start + 1;
    SET @end = PATINDEX('%[^\]["]%', RIGHT(@JSON, LEN(@JSON + '|') - @Start) COLLATE SQL_Latin1_General_CP850_BIN);
  END
  IF (@end = 0)
    BREAK
  SELECT
    @token = SUBSTRING(@JSON, @Start + 1, @end - 1)
  SELECT
    @token = REPLACE(@token, FromString, ToString)
  FROM (SELECT
      '\"' AS FromString
     ,'"' AS ToString
    UNION ALL
    SELECT
      '\\'
     ,'\'
    UNION ALL
    SELECT
      '\/'
     ,'/'
    UNION ALL
    SELECT
      '\b'
     ,CHAR(08)
    UNION ALL
    SELECT
      '\f'
     ,CHAR(12)
    UNION ALL
    SELECT
      '\n'
     ,CHAR(10)
    UNION ALL
    SELECT
      '\r'
     ,CHAR(13)
    UNION ALL
    SELECT
      '\t'
     ,CHAR(09)) substitutions
  SELECT
    @result = 0
   ,@Escape = 1
  WHILE (@Escape > 0)
  BEGIN
  SELECT
    @index = 0
   ,@Escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token COLLATE SQL_Latin1_General_CP850_BIN)
  IF (@Escape > 0)
  BEGIN
    WHILE (@index < 4)
    BEGIN
    SELECT
      @result = @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @Escape + 2 + 3 - @index, 1), @characters) - 1)
     ,@index = @index + 1;
    END
    SELECT
      @token = STUFF(@token, @Escape, 6, NCHAR(@result))
  END
  END
  INSERT INTO @Strings (Ds_String)
    SELECT
      @token
  SELECT
    @JSON = STUFF(@JSON, @Start, @end + 1, '@string' + CONVERT(NVARCHAR(5), @@IDENTITY))
  END
  WHILE (1 = 1)
  BEGIN
  SELECT
    @Id_Object_Parent = @Id_Object_Parent + 1
  SELECT
    @FirstObject = PATINDEX('%[{[[]%', @JSON COLLATE SQL_Latin1_General_CP850_BIN)
  IF (@FirstObject = 0)
    BREAK
  IF (SUBSTRING(@JSON, @FirstObject, 1) = '{')
    SELECT
      @NextCloseDelimiterChar = '}'
     ,@Type = 'object'
  ELSE
    SELECT
      @NextCloseDelimiterChar = ']'
     ,@Type = 'array'
  SELECT
    @OpenDelimiter = @FirstObject
  WHILE (1 = 1)
  BEGIN
  SELECT
    @lenJSON = LEN(@JSON + '|') - 1
  SELECT
    @NextCloseDelimiter = CHARINDEX(@NextCloseDelimiterChar, @JSON, @OpenDelimiter + 1)
  SELECT
    @NextOpenDelimiter = PATINDEX('%[{[[]%', RIGHT(@JSON, @lenJSON - @OpenDelimiter) COLLATE SQL_Latin1_General_CP850_BIN)
  IF (@NextOpenDelimiter = 0)
    BREAK
  SELECT
    @NextOpenDelimiter = @NextOpenDelimiter + @OpenDelimiter
  IF (@NextCloseDelimiter < @NextOpenDelimiter)
    BREAK
  IF SUBSTRING(@JSON, @NextOpenDelimiter, 1) = '{'
    SELECT
      @NextCloseDelimiterChar = '}'
     ,@Type = 'object'
  ELSE
    SELECT
      @NextCloseDelimiterChar = ']'
     ,@Type = 'array'
  SELECT
    @OpenDelimiter = @NextOpenDelimiter
  END
  SELECT
    @Contents = SUBSTRING(@JSON, @OpenDelimiter + 1, @NextCloseDelimiter - @OpenDelimiter - 1)
  SELECT
    @JSON = STUFF(@JSON, @OpenDelimiter, @NextCloseDelimiter - @OpenDelimiter + 1, '@' + @Type + CONVERT(NVARCHAR(5), @Id_Object_Parent))
  WHILE ((PATINDEX('%[A-Za-z0-9@+.e]%', @Contents COLLATE SQL_Latin1_General_CP850_BIN)) <> 0)
  BEGIN
  IF (@Type = 'Object')
  BEGIN
    SELECT
      @No_Sequence = 0
     ,@end = CHARINDEX(':', ' ' + @Contents)
    SELECT
      @Start = PATINDEX('%[^A-Za-z@][@]%', ' ' + @Contents COLLATE SQL_Latin1_General_CP850_BIN)--AAAAAAAA
    SELECT
      @token = SUBSTRING(' ' + @Contents, @Start + 1, @end - @Start - 1)
     ,@EndOfDs_Name = PATINDEX('%[0-9]%', @token COLLATE SQL_Latin1_General_CP850_BIN)
     ,@param = RIGHT(@token, LEN(@token) - @EndOfDs_Name + 1)
    SELECT
      @token = LEFT(@token, @EndOfDs_Name - 1)
     ,@Contents = RIGHT(' ' + @Contents, LEN(' ' + @Contents + '|') - @end - 1)
    SELECT
      @Ds_Name = Ds_String
    FROM @Strings
    WHERE String_ID = @param
  END
  ELSE
    SELECT
      @Ds_Name = NULL
     ,@No_Sequence = @No_Sequence + 1
  SELECT
    @end = CHARINDEX(',', @Contents)
  IF (@end = 0)
    SELECT
      @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents + ' ' COLLATE SQL_Latin1_General_CP850_BIN) + 1
  SELECT
    @Start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @Contents COLLATE SQL_Latin1_General_CP850_BIN)
  SELECT
    @value = RTRIM(SUBSTRING(@Contents, @Start, @end - @Start))
   ,@Contents = RIGHT(@Contents + ' ', LEN(@Contents + '|') - @end)
  IF (SUBSTRING(@value, 1, 7) = '@object')
  BEGIN
    INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Id_Object, Ds_Type)
      SELECT
        @Ds_Name
       ,@No_Sequence
       ,@Id_Object_Parent
       ,SUBSTRING(@value, 8, 5)
       ,SUBSTRING(@value, 8, 5)
       ,'object'
  END
  ELSE
  BEGIN
    IF (SUBSTRING(@value, 1, 6) = '@array')
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Id_Object, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,SUBSTRING(@value, 7, 5)
         ,SUBSTRING(@value, 7, 5)
         ,'array'
    ELSE
    IF (SUBSTRING(@value, 1, 7) = '@string')
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,Ds_String
         ,'string'
        FROM @Strings
        WHERE String_ID = SUBSTRING(@value, 8, 5)
    ELSE
    IF (@value IN ('true', 'false'))
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,@value
         ,'boolean'
    ELSE
    IF (@value = 'null')
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,@value
         ,'null'
    ELSE
    IF (PATINDEX('%[^0-9]%', @value COLLATE SQL_Latin1_General_CP850_BIN) > 0)
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,@value
         ,'real'
    ELSE
      INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Ds_Type)
        SELECT
          @Ds_Name
         ,@No_Sequence
         ,@Id_Object_Parent
         ,@value
         ,'int'
    IF (@Contents = ' ')
      SELECT
        @No_Sequence = 0
  END
  END
  END
  INSERT INTO @Retorno (Ds_Name, No_Sequence, Id_Object_Parent, Ds_String, Id_Object, Ds_Type)
    SELECT
      '-'
     ,1
     ,NULL
     ,''
     ,@Id_Object_Parent - 1
     ,@Type
  DECLARE @Tabela_Final TABLE (
    Id_Element INT IDENTITY (1, 1) NOT NULL
   ,No_Sequence [INT] NULL
   ,Id_Object_Parent INT
   ,Id_Object INT
   ,Ds_Name NVARCHAR(2000)
   ,Ds_String NVARCHAR(MAX) NOT NULL
   ,Ds_Type VARCHAR(10) NOT NULL
  )
  INSERT INTO @Tabela_Final
    SELECT
      No_Sequence
     ,Id_Object_Parent
     ,Id_Object
     ,Ds_Name
     ,Ds_String
     ,Ds_Type
    FROM @Retorno
    ORDER BY ISNULL(Id_Object, Id_Object_Parent) DESC,
    Id_Object_Parent DESC,
    Id_Element
  DELETE FROM @Retorno
  INSERT INTO @Retorno
    SELECT
      Id_Element
     ,No_Sequence
     ,Id_Object_Parent
     ,Id_Object
     ,Ds_Name
     ,Ds_String
     ,Ds_Type
    FROM @Tabela_Final
  RETURN
END
GO

 

Cara pemakaian fungsi dengan perintah / contoh dibawah

DECLARE @jsonData NVARCHAR(MAX);
SET @jsonData = '[{"item": "Kecap","harga": "5000"},{"item": "Merica","harga": "7500"},{"item": "Susu Beruang","harga": "15000"}]';

-- DEFAULT RESULT
SELECT * FROM dbo.fnJSONRead(@jsonData)

-- GROUPING
SELECT
tableValue.Id_Object_Parent
,tableValue.Ds_Name
,tableValue.Ds_String
FROM dbo.fnJSONRead(@jsonData) tableKey
INNER JOIN dbo.fnJSONRead(@jsonData) tableValue
ON tableKey.Id_Object_Parent = tableValue.Id_Object_Parent
WHERE tableKey.Ds_Name IS NOT NULL
GROUP BY tableValue.Id_Object_Parent
,tableValue.Ds_Name
,tableValue.Ds_String

-- FINAL RESULT: PIVOT TABLE
SELECT
item
,harga
FROM (SELECT
tableValue.Id_Object_Parent
,tableValue.Ds_Name
,tableValue.Ds_String
FROM dbo.fnJSONRead(@jsonData) tableKey
INNER JOIN dbo.fnJSONRead(@jsonData) tableValue
ON tableKey.Id_Object_Parent = tableValue.Id_Object_Parent
WHERE tableKey.Ds_Name IS NOT NULL
GROUP BY tableValue.Id_Object_Parent
,tableValue.Ds_Name
,tableValue.Ds_String) AS T
PIVOT (
MAX(T.Ds_String) FOR Ds_Name IN (item, harga)
) AS PT

Dengan perintah diatas didapat hasil sebagai berikut:

JsonToTable - 01
Select tanpa grouping (Default)

 

Select Menggunakan Grouping
Select Menggunakan Grouping

 

Select Menggunakan Grouping
Select Menggunakan Grouping dan Pivot

Untuk Database bisa di Download disini: Google Drive .

Tags

About suhendrayputra

Avatar of suhendrayputra
Konsultan IT Bandung, Freelance web developer Kota Bandung. Jasa Pembuatan Website dan Aplikasi Sistem Informasi. #java #aspnet #wordPress

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Powered by WordPress | Designed by TieLabs
© Copyright 2024, All Rights Reserved