ODBC Azure Blob Driver for JSON Files

This example shows how to query single JSON file (For multiple file use wildcard (i.e. *.json)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).

SELECT * FROM $ WITH (SRC='zappysys-public-bucket/cust-1.json')

Open this example in the help

This example shows how to query multiple JSON files using wildcard (i.e. *.json). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).

SELECT * FROM $
WITH (
    SRC='zappysys-public-bucket/cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)

Open this example in the help

This example shows how to query direct JSON string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call).

SELECT * FROM rows
WITH
(DATA='
{
  rows : [
        {id:1, name: "AAA"},
        {id:2, name: "BBB"},
        {id:3, name: "CCC"}
  ]
}'
)

Open this example in the help

This example shows how to supply custom Metadata rather than relying dynamic metadata detection (based on 300 rows scanning). If you run query you can click on [View Metadata] button to generate default metadata.

SELECT * FROM rows
WITH
(DATA='
{
  rows : [
        {id:1, checknumber:"0001", name: "AAA"},
        {id:2, checknumber:"0002", name: "BBB"},
        {id:3, checknumber:"0003", name: "CCC"}
  ]
}'
--//Method-1: >>>DIRECT META (compact mode)<<<
,META='id:int;checknumber:string(10);name:string(10)'
-- Add: @OverrideMode:1 in the column list to override meta for only specific column(s) - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='@OverrideMode:1;id:int;checknumber:string(10)'

--//Method-2: >>>DIRECT META (json mode)<<<
--,META='[{Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10},{Name:"name",Type:"String",Length:10}]'
-- Add: @OverrideMode entry in the column list to override meta for only specific column(s). No need to set Type for that entry - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--,META='[{Name:"@OverrideMode"}, {Name:"id",Type:"Int32"},{Name:"checknumber",Type:"String",Length:10}]'

--//Method-3: >>>META from File <<<
--,META='c:/data/meta.json'

--//Method-4: >>>META from Internal Storage - Just refer entry by name <<<
--,META='MY_META_STORAGE_NAME'

)

Open this example in the help

This example shows how to use escape sequence in String Literals (Quoted values (single quotes) in WITH, SELECT or some other clause if driver supports). You can use @ symbol before string literal to allow special characters (e.g. /r /n /t /v /u0041 //).

SELECT * FROM rows
WITH
(DATA=@'{rows : [{id:1, name: "AA\u0041"},\r\n {id:2, name: "BBB"},\r\n {id:3, name: "CCC"}]}'
)

Open this example in the help

This example shows how to read very large file from local disk or URL. You can read uncompressed or compressed file (in our example its GZip compressed file). Using –FAST option in your Filter enables Streaming Mode.

SELECT * FROM $
--LIMIT 10
WITH(
 Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
,SRC='zappysys-public-bucket/large_file_10k_largearray_prop.json.gz'
--,SRC='zappysys-public-bucket/large_file*.json.gz'
,IncludeParentColumns='False'  --//This Must be OFF for STREAM mode (read very large files)
,FileCompressionType='GZip' --Zip or None (Zip format only available for Local files)
)

Open this example in the help

This example shows how to query compact 2D array (array inside array) where column name is not repeated. You can specify Simple 2D Transformation along with filter to select column name (e.g. $.columns[*])

SELECT * FROM rows
WITH(
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.json'
--SRC='zappysys-public-bucket/some*.json'
--SRC='zappysys-public-bucket/somefile.json'
DATA='
{
  "columns": ["RecordID","CustomerID","CustomerName"],
  "rows": [ [1,"AAA","Customer A"], [2,"BBB","Customer B"], [3,"CCC","Customer C"] ]
}'
,ArrayTransformType='TransformSimpleTwoDimensionalArray'
,ArrayTransColumnNameFilter='$.columns[*]'
)

Open this example in the help

This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT
  substr('ZappySys', 1, 5)
, printf('%s %s %.2f', 'v1', CompanyName, CompanyId)
, ('Str1' || CompanyName || 'Str2') as ConcatExample
, upper('ZappySys')
, lower('ZappySys')
, replace('ZappySys', 'Sys', 'XYZ')
, instr('ZappySys', 'Sys')
, trim('   Zappy Sys  ') trim1
, trim('@@@ZappySys@@@', '@') trim2
, rtrim('    ZappySys   ')
, ltrim('    ZappySys   ')
, length( CompanyName )
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='{ Info: { Rows: [ { CompanyId: 1000 , CompanyName: "ZappySys" }, { CompanyId: 1001 , CompanyName: "Microsoft" } ] } }'
)

Open this example in the help

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT
	 COALESCE(10,20) coalesce1 --returns first non-null arg
    ,COALESCE(null,20) coalesce2 --returns first non-null arg
    ,COALESCE(null,20,30,40) coalesce3 --returns first non-null arg
    ,IFNULL(null,20) --returns first non-null arg. Same function as COALESCE but simple (just two arguments allowed)
    ,NULLIF(20,20) nullif1 --returns null if both argument same
    ,NULLIF(10,20) nullif2 --returns null if both argument same
FROM $ WITH (
FILTER='$.Info.Rows[*]',
DATA='{ Info: { Rows : [ { CompanyId: 1000 , CompanyName: "ZappySys" }, { CompanyId: 1001 , CompanyName: "Microsoft" }] } }')

Open this example in the help

This example shows how to use date/time functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation. JSON driver supports following 5 datetime functions.

SELECT
 DATE('now') date_now
,STRFTIME('%Y-%m-%dT%H:%M:%f','now') formatted_Date_time
,DATETIME('now') datetime_now
,DATE('now','localtime') datetime_now_local
,TIME('now') time_now
,JULIANDAY('now')
,DATE('now', 'start of month', '+1 month' , '-1 day' ) dt_modify1
,DATE('now', '+5 day') dt_modify2

FROM $ WITH (DATA='{ Info: { Rows : [ { CompanyId: 1000 , CompanyName: "ZappySys" }, { CompanyId: 1001 , CompanyName: "Microsoft" } ] } }')

Open this example in the help

This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.

SELECT
	 abs(-500)
    ,random()
    ,round(1000.236, 2)
FROM $  WITH (DATA='{ Info: { Rows: [ { CompanyId: 1000 , CompanyName: "ZappySys" }, { CompanyId: 1001 , CompanyName: "Microsoft" } ] } }')

Open this example in the help

This example shows how to pivot column to rows (e.g. Transform property name as row value). By default Tag name (i.e. Property) outputs as Column name but when your column name is Dynamic then you can use Pivot Feature to transform the data.

SELECT
  Pivot_Name as ProjectName,
  Pivot_Value_id as Id,
  Pivot_Value_code as Code,
  Pivot_Value_budget as Budget
FROM $
WITH(
Filter='$.projects',
--enter path in SRC or use static value in DATA'
--SRC='zappysys-public-bucket/api/data.json'
--SRC='zappysys-public-bucket/some*.json'
--SRC='zappysys-public-bucket/somefile.json'
DATA='
{
  projects : {
    P101 : {id:1, code: "AAA", budget:1500.10},
    P102 : {id:2, code: "BBB", budget:7000.20},
    P103 : {id:3, code: "CCC", budget:1100.10}
  }
}
',
EnablePivot='True'
)

Open this example in the help

This example shows how to use various JSON functions.

select
json_value(j1,"$.V1"),
json_array_first(j2),
json_array_last(j2),
json_array_nth(j2,1)
from array WITH (DATA=
'{"array" : [{"c1": "abc", "c2": "ABC", "j1": "{''V1'':''value1''}", "j2": "[13,22,''31'']"}] }')

Open this example in the help

Query the driver from SQL Server through the Data Gateway. The driver does not have to be installed on SQL Server.

-- SQL Server: create a linked server to the ZappySys Data Gateway (here [ZAPPY_GATEWAY]),
-- then run the driver query through OPENQUERY.
SELECT *
FROM OPENQUERY([ZAPPY_GATEWAY], 'SELECT * FROM $
WITH (
    SRC=''zappysys-public-bucket/cust*-?.json''
    --,RECURSIVE=''True'' --Include files from sub folder
)')

using System;
using System.Data.Odbc;

// Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
using (var conn = new OdbcConnection("DRIVER={ZappySys Azure Blob JSON Driver};"))
{
    conn.Open();
    var cmd = new OdbcCommand(@"SELECT * FROM $
WITH (
    SRC='zappysys-public-bucket/cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)", conn);
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            for (int i = 0; i < rdr.FieldCount; i++)
                Console.Write("{0}={1}  ", rdr.GetName(i), rdr[i]);
            Console.WriteLine();
        }
    }
}

import pyodbc

# Use a DSN ("DSN=MyZappyDsn;") or name the driver directly
conn = pyodbc.connect("DRIVER={ZappySys Azure Blob JSON Driver};")
cursor = conn.cursor()
cursor.execute(r"""
SELECT * FROM $
WITH (
    SRC='zappysys-public-bucket/cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)
""")
for row in cursor.fetchall():
    print(row)
conn.close()

$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DRIVER={ZappySys Azure Blob JSON Driver};"   # or "DSN=MyZappyDsn"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = @'
SELECT * FROM $
WITH (
    SRC='zappysys-public-bucket/cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)
'@
$table = New-Object System.Data.DataTable
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($table) | Out-Null
$conn.Close()
$table | Format-Table

// Java uses the ZappySys Data Gateway and the Microsoft SQL Server JDBC driver.
// On the gateway, add a data source (here "MyZappySource") that uses the ZappySys Azure Blob JSON Driver.
import java.sql.*;

String url = "jdbc:sqlserver://localhost:5000;databaseName=MyZappySource;user=MyGatewayUser;password=MyPassword;encrypt=false";
String sql = "SELECT * FROM $\n"
           + "WITH (\n"
           + "    SRC='zappysys-public-bucket/cust*-?.json'\n"
           + "    --,RECURSIVE='True' --Include files from sub folder\n"
           + ")";
try (Connection conn = DriverManager.getConnection(url);
     Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery(sql)) {
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
}

<?php
$conn = odbc_connect("DRIVER={ZappySys Azure Blob JSON Driver};", "", "");   // or "DSN=MyZappyDsn"
$sql = <<<'SQL'
SELECT * FROM $
WITH (
    SRC='zappysys-public-bucket/cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)
SQL;
$rs = odbc_exec($conn, $sql);
while ($row = odbc_fetch_array($rs)) {
    print_r($row);
}
odbc_close($conn);