.::: Create table MSSQL SQL Server Support JSON_QUERY,JSON PATH & Output Json :::.

 

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT
-- Barang
SELECT
    JSON_QUERY((
        SELECT
            KODE_BARANG AS 'KODE_BARANG',
            NAMA_BARANG AS 'NAMA_BARANG',
            SATUAN_BARANG AS 'SATUAN_BARANG',
            STOK_BARANG AS 'STOK_BARANG'
        FOR JSON PATH
    )) AS barang_jsonarr
FROM barang;
 
-- Customer
SELECT
    JSON_QUERY((
        SELECT
            KODE_CUSTOMER AS 'KODE_CUSTOMER',
            NAMA_CUSTOMER AS 'NAMA_CUSTOMER',
            ALAMAT_CUSTOMER AS 'ALAMAT_CUSTOMER',
            KOTA_CUSTOMER AS 'KOTA_CUSTOMER',
            TELEPON_CUSTOMER AS 'TELEPON_CUSTOMER'
        FOR JSON PATH
    )) AS customer_jsonarr
FROM customer;

-- Suplier
SELECT
    JSON_QUERY((
        SELECT
            KODE_SUPLIER AS 'KODE_SUPLIER',
            NAMA_SUPLIER AS 'NAMA_SUPLIER',
            ALAMAT_SUPLIER AS 'ALAMAT_SUPLIER',
            KOTA_SUPLIER AS 'KOTA_SUPLIER',
            TELEPON_SUPLIER AS 'TELEPON_SUPLIER'
        FOR JSON PATH
    )) AS suplier_jsonarr
FROM suplier;

-- Pasok
SELECT
    JSON_QUERY((
        SELECT
            KODE_PASOK AS 'KODE_PASOK',
            KODE_BARANG AS 'KODE_BARANG',
            KODE_SUPLIER AS 'KODE_SUPLIER',
            TANGGAL_PASOK AS 'TANGGAL_PASOK',
            JUMLAH_PASOK AS 'JUMLAH_PASOK'
        FOR JSON PATH
    )) AS pasok_jsonarr
FROM pasok;

-- Pembelian
SELECT
    JSON_QUERY((
        SELECT
            KODE_PEMBELIAN AS 'KODE_PEMBELIAN',
            KODE_BARANG AS 'KODE_BARANG',
            KODE_CUSTOMER AS 'KODE_CUSTOMER',
            TANGGAL_PEMBELIAN AS 'TANGGAL_PEMBELIAN',
            JUMLAH_PEMBELIAN AS 'JUMLAH_PEMBELIAN'
        FOR JSON PATH
    )) AS pembelian_jsonarr
FROM pembelian;
 

2. run query to ouput JSON_ARRAYAGG
-- Barang
SELECT
    (SELECT
        KODE_BARANG AS 'KODE_BARANG',
        NAMA_BARANG AS 'NAMA_BARANG',
        SATUAN_BARANG AS 'SATUAN_BARANG',
        STOK_BARANG AS 'STOK_BARANG'
    FROM barang
    FOR JSON PATH) AS barang_jsonarr;

-- Customer
SELECT
    (SELECT
        KODE_CUSTOMER AS 'KODE_CUSTOMER',
        NAMA_CUSTOMER AS 'NAMA_CUSTOMER',
        ALAMAT_CUSTOMER AS 'ALAMAT_CUSTOMER',
        KOTA_CUSTOMER AS 'KOTA_CUSTOMER',
        TELEPON_CUSTOMER AS 'TELEPON_CUSTOMER'
    FROM customer
    FOR JSON PATH) AS customer_jsonarr;

-- Suplier
SELECT
    (SELECT
        KODE_SUPLIER AS 'KODE_SUPLIER',
        NAMA_SUPLIER AS 'NAMA_SUPLIER',
        ALAMAT_SUPLIER AS 'ALAMAT_SUPLIER',
        KOTA_SUPLIER AS 'KOTA_SUPLIER',
        TELEPON_SUPLIER AS 'TELEPON_SUPLIER'
    FROM suplier
    FOR JSON PATH) AS suplier_jsonarr;

-- Pasok
SELECT
    (SELECT
        KODE_PASOK AS 'KODE_PASOK',
        KODE_BARANG AS 'KODE_BARANG',
        KODE_SUPLIER AS 'KODE_SUPLIER',
        TANGGAL_PASOK AS 'TANGGAL_PASOK',
        JUMLAH_PASOK AS 'JUMLAH_PASOK'
    FROM pasok
    FOR JSON PATH) AS pasok_jsonarr;

-- Pembelian
SELECT
    (SELECT
        KODE_PEMBELIAN AS 'KODE_PEMBELIAN',
        KODE_BARANG AS 'KODE_BARANG',
        KODE_CUSTOMER AS 'KODE_CUSTOMER',
        TANGGAL_PEMBELIAN AS 'TANGGAL_PEMBELIAN',
        JUMLAH_PEMBELIAN AS 'JUMLAH_PEMBELIAN'
    FROM pembelian
    FOR JSON PATH) AS pembelian_jsonarr;
 

B. Create table table with support JSON

1. create table ;
-- Tabel barang_json
CREATE TABLE barang_json (
    KODE_BARANG CHAR(6) NOT NULL,
    NAMA_BARANG VARCHAR(25),
    SATUAN_BARANG VARCHAR(20),
    STOK_BARANG DECIMAL(4, 0),
    INFO_JSON NVARCHAR(MAX) -- JSON di SQL Server disimpan sebagai NVARCHAR
);

-- Tabel customer_json
CREATE TABLE customer_json (
    KODE_CUSTOMER CHAR(6) NOT NULL,
    NAMA_CUSTOMER VARCHAR(30),
    ALAMAT_CUSTOMER VARCHAR(30),
    KOTA_CUSTOMER VARCHAR(15),
    TELEPON_CUSTOMER VARCHAR(15),
    CUSTOMER_DETAILS NVARCHAR(MAX) -- JSON di SQL Server disimpan sebagai NVARCHAR
);

-- Tabel suplier_json
CREATE TABLE suplier_json (
    KODE_SUPLIER CHAR(5) NOT NULL,
    NAMA_SUPLIER VARCHAR(30),
    ALAMAT_SUPLIER VARCHAR(30),
    KOTA_SUPLIER VARCHAR(15),
    TELEPON_SUPLIER VARCHAR(15),
    SUPPLIER_DETAILS NVARCHAR(MAX) -- JSON di SQL Server disimpan sebagai NVARCHAR
);

-- Tabel pasok_json
CREATE TABLE pasok_json (
    KODE_PASOK CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_SUPLIER CHAR(5) NOT NULL,
    TANGGAL_PASOK DATE,
    JUMLAH_PASOK DECIMAL(4, 0),
    PASOK_DETAILS NVARCHAR(MAX) -- JSON di SQL Server disimpan sebagai NVARCHAR
);

-- Tabel pembelian_json
CREATE TABLE pembelian_json (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE,
    JUMLAH_PEMBELIAN DECIMAL(4, 0),
    PEMBELIAN_DETAILS NVARCHAR(MAX) -- JSON di SQL Server disimpan sebagai NVARCHAR
);
 

2. insert data

INSERT INTO barang_json (KODE_BARANG, NAMA_BARANG, SATUAN_BARANG, STOK_BARANG, INFO_JSON)
VALUES
('ELK-01', 'RICE COOKER', 'BUAH', 20, '{"warranty": "2 years", "brand": "ABC", "color": "red"}'),
('ELK-02', 'LEMARI ES', 'UNIT', 8, '{"warranty": "3 years", "brand": "XYZ", "energy_rating": "A+"}');

INSERT INTO customer_json (KODE_CUSTOMER, NAMA_CUSTOMER, ALAMAT_CUSTOMER, KOTA_CUSTOMER, TELEPON_CUSTOMER, CUSTOMER_DETAILS)
VALUES
('B-0001', 'TOKO WARNA', 'JL ABC 234', 'BANDUNG', '(022) 432-6635', '{"email": "tokowarna@example.com", "status": "active"}'),
('B-0002', 'TOKO SURYA', 'JL ABC 309', 'BANDUNG', '(022) 432-6024', '{"email": "tokosurya@example.com", "status": "inactive"}');

INSERT INTO suplier_json (KODE_SUPLIER, NAMA_SUPLIER, ALAMAT_SUPLIER, KOTA_SUPLIER, TELEPON_SUPLIER, SUPPLIER_DETAILS)
VALUES
('EB-01', 'PT ULTRASOUND', 'JL SUKARNO HATTA 103', 'BANDUNG', '(021) 522-3305', '{"email": "ultrasound@example.com", "rating": 4.5}'),
('EB-02', 'PT SUPERTRON', 'JL INDUSTRI 37', 'BANDUNG', '(021) 660-4091', '{"email": "supertron@example.com", "rating": 4.0}');

INSERT INTO pasok_json (KODE_PASOK, KODE_BARANG, KODE_SUPLIER, TANGGAL_PASOK, JUMLAH_PASOK, PASOK_DETAILS)
VALUES
('PSK-001', 'ELK-01', 'EB-01', '2025-01-15', 100, '{"status": "Delivered", "lokasi_pengiriman": "Gudang A"}'),
('PSK-002', 'ELK-02', 'EB-02', '2025-01-16', 150, '{"status": "In Progress", "lokasi_pengiriman": "Gudang B"}');

INSERT INTO pembelian_json (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN, PEMBELIAN_DETAILS)
VALUES
('BEL-P001', 'ELK-01', 'J-0001', '2025-01-10', 3, '{"metode_pembayaran": "Transfer Bank", "status": "Selesai"}'),
('BEL-P002', 'ELK-02', 'J-0002', '2025-01-12', 5, '{"metode_pembayaran": "Tunai", "status": "Pending"}');

3. select data

select * from barang_json;
select * from customer_json;
select * from suplier_json;
select * from pasok_json;
select * from pembelian_json;