.::: Read Json file using OPENJSON, OPENROWSET in SQL Server :::.

 

1. copy json sample to disk C:\bcp\teguhth.pembelian.json
 

 

2. only read json 
 
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'C:\bcp\teguhth.pembelian.json',
    SINGLE_CLOB
) AS j;
 

3. read json 

SELECT value
FROM OPENROWSET(
    BULK 'C:\bcp\teguhth.pembelian.json',
    SINGLE_CLOB
) AS j
CROSS APPLY OPENJSON(j.BulkColumn);
 

4. only convert to column

SELECT 
    KODE_PEMBELIAN,
    KODE_BARANG,
    KODE_CUSTOMER,
    TANGGAL_PEMBELIAN,
    JUMLAH_PEMBELIAN
FROM OPENROWSET(
    BULK 'C:\bcp\teguhth.pembelian.json',
    SINGLE_CLOB
) AS j
CROSS APPLY OPENJSON(j.BulkColumn)
WITH (
    KODE_PEMBELIAN varchar(20),
    KODE_BARANG varchar(20),
    KODE_CUSTOMER varchar(20),
    TANGGAL_PEMBELIAN varchar(20),
    JUMLAH_PEMBELIAN int
);
 

5. read file json every baris

SELECT 
    j2.value AS json_raw,   -- JSON utuh per baris
    p.*
FROM OPENROWSET(
    BULK 'C:\bcp\teguhth.pembelian.json',
    SINGLE_CLOB
) AS j
CROSS APPLY OPENJSON(j.BulkColumn) AS j2
CROSS APPLY OPENJSON(j2.value)
WITH (
    KODE_PEMBELIAN varchar(20),
    KODE_BARANG varchar(20),
    KODE_CUSTOMER varchar(20),
    TANGGAL_PEMBELIAN varchar(20),
    JUMLAH_PEMBELIAN int
) AS p;
 

6. direct openjson read 

DECLARE @json NVARCHAR(MAX) = N'[  
{
  "_id": "BEL-E001",
  "KODE_PEMBELIAN": "BEL-E001",
  "KODE_BARANG": "ELK-01",
  "KODE_CUSTOMER": "J-0001",
  "TANGGAL_PEMBELIAN": "20-May-02",
  "JUMLAH_PEMBELIAN": "3"
},
{
  "_id": "BEL-E002",
  "KODE_PEMBELIAN": "BEL-E002",
  "KODE_BARANG": "ELK-01",
  "KODE_CUSTOMER": "J-0001",
  "TANGGAL_PEMBELIAN": "21-May-02",
  "JUMLAH_PEMBELIAN": "4"
},
{
  "_id": "BEL-E003",
  "KODE_PEMBELIAN": "BEL-E003",
  "KODE_BARANG": "ELK-01",
  "KODE_CUSTOMER": "J-0002",
  "TANGGAL_PEMBELIAN": "20-May-02",
  "JUMLAH_PEMBELIAN": "2"
},
{
  "_id": "BEL-E004",
  "KODE_PEMBELIAN": "BEL-E004",
  "KODE_BARANG": "ELK-01",
  "KODE_CUSTOMER": "B-0001",
  "TANGGAL_PEMBELIAN": "20-May-02",
  "JUMLAH_PEMBELIAN": "2"
},
{
  "_id": "BEL-E005",
  "KODE_PEMBELIAN": "BEL-E005",
  "KODE_BARANG": "ELK-01",
  "KODE_CUSTOMER": "B-0002",
  "TANGGAL_PEMBELIAN": "22-May-02",
  "JUMLAH_PEMBELIAN": "3"
},
{
  "_id": "BEL-E006",
  "KODE_PEMBELIAN": "BEL-E006",
  "KODE_BARANG": "ELK-02",
  "KODE_CUSTOMER": "J-0001",
  "TANGGAL_PEMBELIAN": "24-Jun-02",
  "JUMLAH_PEMBELIAN": "1"
},
{
  "_id": "BEL-E007",
  "KODE_PEMBELIAN": "BEL-E007",
  "KODE_BARANG": "ELK-02",
  "KODE_CUSTOMER": "J-0002",
  "TANGGAL_PEMBELIAN": "24-Jun-02",
  "JUMLAH_PEMBELIAN": "1"
},
{
  "_id": "BEL-E008",
  "KODE_PEMBELIAN": "BEL-E008",
  "KODE_BARANG": "ELK-02",
  "KODE_CUSTOMER": "B-0001",
  "TANGGAL_PEMBELIAN": "25-Jun-02",
  "JUMLAH_PEMBELIAN": "2"
},
{
  "_id": "BEL-E009",
  "KODE_PEMBELIAN": "BEL-E009",
  "KODE_BARANG": "ELK-02",
  "KODE_CUSTOMER": "B-0002",
  "TANGGAL_PEMBELIAN": "25-Jun-02",
  "JUMLAH_PEMBELIAN": "2"
},
{
  "_id": "BEL-E010",
  "KODE_PEMBELIAN": "BEL-E010",
  "KODE_BARANG": "ELK-03",
  "KODE_CUSTOMER": "J-0001",
  "TANGGAL_PEMBELIAN": "20-Jun-02",
  "JUMLAH_PEMBELIAN": "5"
},
{
  "_id": "BEL-E011",
  "KODE_PEMBELIAN": "BEL-E011",
  "KODE_BARANG": "ELK-03",
  "KODE_CUSTOMER": "J-0002",
  "TANGGAL_PEMBELIAN": "2-Jul-02",
  "JUMLAH_PEMBELIAN": "4"
},
{
  "_id": "BEL-E012",
  "KODE_PEMBELIAN": "BEL-E012",
  "KODE_BARANG": "ELK-03",
  "KODE_CUSTOMER": "B-0001",
  "TANGGAL_PEMBELIAN": "4-Jul-02",
  "JUMLAH_PEMBELIAN": "6"
},
{
  "_id": "BEL-E013",
  "KODE_PEMBELIAN": "BEL-E013",
  "KODE_BARANG": "ELK-03",
  "KODE_CUSTOMER": "J-0001",
  "TANGGAL_PEMBELIAN": "10-Jul-02",
  "JUMLAH_PEMBELIAN": "5"
},
{
  "_id": "BEL-E014",
  "KODE_PEMBELIAN": "BEL-E014",
  "KODE_BARANG": "ELK-04",
  "KODE_CUSTOMER": "J-0002",
  "TANGGAL_PEMBELIAN": "15-Jul-02",
  "JUMLAH_PEMBELIAN": "12"
},
{
  "_id": "BEL-E015",
  "KODE_PEMBELIAN": "BEL-E015",
  "KODE_BARANG": "ELK-04",
  "KODE_CUSTOMER": "B-0002",
  "TANGGAL_PEMBELIAN": "17-Jul-02",
  "JUMLAH_PEMBELIAN": "15"
}
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (
    KODE_PEMBELIAN varchar(20),
    KODE_BARANG varchar(20),
    KODE_CUSTOMER varchar(20),
    TANGGAL_PEMBELIAN varchar(20),
    JUMLAH_PEMBELIAN int
);