SQL Query Examples
PreviousNext

On this page you will find various query examples for Json ODBC Driver.

SQL Syntax


SELECT 
 [* | [ expression [[AS] column_name_alias] [, ...] ]
 [FROM table_name]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 
 [WITH  (option_name=option_value] [, ...]) ]  

Query JSON API Service

This example shows how to query JSON API service. In order to see table list you must populate required connection parameters on the parameter grid
SELECT * FROM value 
WITH (SRC='https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json')

Query Single File

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). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.

SELECT * FROM $ WITH (SRC='C:\Data\cust-1.json')

Query Multiple Files

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='C:\Data\cust*-?.json'
    --,RECURSIVE='True' --Include files from sub folder
)

Query Large Compressed File (From URL or Local disk)

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. To use STREAM Mode you must turn off IncludeParentColumns option as per the example.
SELECT * FROM $ 
--LIMIT 10
WITH(
 Filter='$.LargeArray[*]--FAST' --//Adding --FAST option turn on STREAM mode (large files)
,SRC='https://zappysys.com/downloads/files/test/large_file_10k_largearray_prop.json.gz'
--,SRC='C:\Data\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)
)

Query direct JSON string (embedded inside query)

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). 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). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.
SELECT * FROM rows 
WITH 
(DATA='
{          
  rows : [
        {id:1, name: "AAA"}, 
        {id:2, name: "BBB"}, 
        {id:3, name: "CCC"}
  ]
}'
)

Query API using Basic Authentication (UserId/Password)

This example shows how to query JSON API service which requires basic authentication. In order to see table list you must populate required connection parameters on the parameter grid

--Credentials via Query
SELECT * FROM $ WITH (SRC='http://httpbin.org/basic-auth/user1/pass123',DataConnectionType='HTTP', CredentialType='Basic',UserName='user1',Password='pass123')

--Credentials inside connection string [Use Data source setup grid >> Select Connection = HTTP (Click Configure) >> Set Credential type=Basic, UserId=user1, Password=pass123]
--SELECT * FROM $ WITH (SRC='http://httpbin.org/basic-auth/user1/pass123')

Query API using NTLM Authentication (Windows Auth)

This example shows how to query JSON API service which requires windows authentication (NTLM).

--Credentials via Query
SELECT * FROM $ 
WITH (SRC='http://yourhost/some-service',DataConnectionType='HTTP',CredentialType='Ntlm')

--Credentials inside connection string [Use Data source setup grid >> Authentication type=Ntlm]
--SELECT * FROM $ WITH (SRC='http://yourhost/some-service')

Query Custom API using OAuth2

This example shows how to query JSON API service which requires OAuth2 authentication.

--Credentials via Query
SELECT * FROM $ WITH (
    SRC='http://yourhost/api/some-service'
    ,DataConnectionType='OAuth'
    ,UseCustomApp='True'
    ,OAuthVersion='OAuth2'
    ,AuthUrl='https://yourhost/api/authorize'
    ,TokenUrl='https://yourhost/api/token'
    ,ClientId='xxxxxxxxxxx'
    ,ClientSecret='xxxxxxxxxxxxx'
    ,AccessToken='xxxxxxxxxxx'
    ,RefreshToken='xxxxxxxxxxx'
)

--Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT * FROM $ WITH (SRC='http://yourhost/api/some-service')

Query REST API (Dynamic Token Method)

This example shows how to call REST API Service using Dynamic Token approach. Learn how to authenticate using userid / password to obtain temporary token which can be passed to call API. Also learn how to pass Body, Headers using HTTP POST. For more information about Dynamic Token visit https://zappysys.com/links/?id=10091
SELECT * FROM $
WITH(
	 Src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=json'
	--,RequestMethod='POST'
	--,RequestData='{sessionid: "[$token$]"}'
	,Filter='$.rows[*]'
	,Header='Content-Type: application/json || Accept: */* || Cache-Control: no-cache'	
	,DataConnectionType='HTTP'
	,CredentialType='TokenDynamic'
	,UserName='MyUser001'
    ,Password='P@$$w0rdAAc12'
	,AuthScheme='{none}'
	,TokenUrl='https://zappysys.com/downloads/files/test/rest-login.aspx'
	,TokenRequestData='{ user:"[$userid$]", password:"[$password$]" }'
	,TokenRequestMethod='POST'
	,TokenResponseContentFilter='$.sessionid'
	,TokenRequestHeaders='Content-Type:application/json | Accept:*/* | Cache-Control:no-cache'
	,TokenResponseContentType='Json'

)

Query Google API using OAuth2

This example shows how to query Google API using OAuth2 authentication. For demo its using Google Analytics API but you can apply same concept for other OAuth2 compatible Google API.

--Credentials via Query
--Check this link to learn more about how to generate ClientId / ClientSecret >> https://zappysys.com/blog/register-google-oauth-application-get-clientid-clientsecret/
--To get Access Token and Refresh Token >> Goto properties grid >> Change Authentication type=OAuth >> Click OAuth Button to configure connection >> Select Provider='Google' >> Use Custom App >> Select Scope and click Generate Token >> Save tokens to file

SELECT * FROM $ WITH
(
 SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns'
,UseCustomApp='True'
,DataConnectionType='OAuth'
,ServiceProvider='Google'
,ClientId='3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com'
,ClientSecret='qF7axxxxxxxxxxxWfs'
,AccessToken='ya29.GlsxxxxxxxxxIam'
,RefreshToken='1/pFOxxxxxxxxxxgU'
)


--Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT * FROM $ WITH (SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns')

Query OData API Service

This example shows how to query OData API. By specifying DataFormat='OData' it will automatically paginate multiple requests for you so you don't have to worry about additional pagination configuration.
SELECT * FROM value WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Order_Details?$format=json',DataFormat='OData')

API Pagination - Paginate based on Response Attribute

This example shows how to paginate API calls where you need to paginate until last page detected. In this example next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching next page.
SELECT * FROM $
WITH(
SRC='https://zappysys.com/downloads/files/test/pagination_nextlink_inarray_1.json'
,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']
)

API Pagination - Paginate based on URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. Driver keeps incrementing page number and calls next URL until last page detected (401 error). There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use default (i.e. No records found).
SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/page-json.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

API Pagination - Paginate based on URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. Driver keeps incrementing page number and calls next URL until last page is detected. There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use default (i.e. No records found).
SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/cust-<%page%>.json'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

URL JOIN Pattern - Pass parent URL data to Child URL

This example shows how to perform URL JOIN. Using this pattern you can pass parent URL data to child API URL in one query. You can pass parent data in child URL or Body (for POST). Placeholder syntax is [$parent_alias.parent_col_name$]. Each level must have ALIAS attribute. For root level you dont have to Prefix JOINxxxxxxx but for any other level you must Prefix JOINxxxxx before each attribute name. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092

SELECT 
    c.custid,c.name,c.city
    ,o.orderid,o.orderdate,o.total
    /* ,i.itemid,i.qty,i.shipped */
FROM $
WITH(
    --1st level (root)--
	 src='https://zappysys.com/downloads/files/test/join/customers.json'
    ,NextUrlAttributeOrExpr='$.nextpage'
	,filter='$.customers[*]'
    ,alias='c'
    ,WaitTimeMs='500'
    
    --2nd level--
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] )
    ,join1_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.json'
    ,join1_filter='$.orders[*]'
    ,join1_alias='o'
    ,join1_NextUrlAttributeOrExpr='$.nextpage'    
    ,join1_WaitTimeMs='500'

    /*
    --3rd level-- 
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
    ,join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.json'
    ,join2_filter='$.items[*]'
    ,join2_alias='i'
    ,join2_NextUrlAttributeOrExpr='$.nextpage'    
    ,join2_WaitTimeMs='500'

    --maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
    -- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
    */
) 

URL JOIN Pattern - Download files to local disk

This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092
select a.url,b.data,file_write_binary(base64_to_bytes(b.data) ,'c:\temp\' || name  ) as bytes_written 
from $
WITH ( 
  SRC='https://zappysys.com/downloads/files/test/imagelist.json',
  FILTER='$.list[*]',
  ALIAS='a',
  
  JOIN1_SRC='[$a.url$]',
  JOIN1_ALIAS='b',
  JOIN1_SaveContentAsBinary='true'  
) 

URL JOIN Pattern - Read unstructured data and pass to API call

This example shows how to query unstructured data and parse using Regex, XPath or JSONPath)

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of JsonPath: https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

Also try following Query Examples to extract Raw data.

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
)

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns<br /> ,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template<br /> ,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)<br />)<br /><br />SELECT * FROM $<br />WITH(<br /> Src='http://httpbin.org/get'<br /> ,EnableRawOutputModeSingleRow='True'<br /> ,RawOutputFilterExpr='$.orgin||$.url' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns<br /> --,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template<br /> ,RawOutputExtractMode='Json' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)<br />)<br /><br /></span> <pre class="codeblock"><code class="sql">SELECT c.title,o.url,o.data FROM $ WITH( Src='http://google.com' ,EnableRawOutputModeSingleRow='True' ,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, JsonPath or XPath. Use double pipe to split multiple columns ,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Json template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes ,RawOutputExtractMode='Regex' --can be Regex, Xml, Json or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path) ,Alias='c' ,join1_RequestMethod='POST' ,join1_RequestData='my data [$c.content$]' ,join1_src='https://httpbin.org/post?id=[$c.title$]' ,join1_RequestContentTypeCode = 'TextPlain' ,join1_alias='o' ,join1_EnableRawOutputModeSingleRow='False' )</code></pre> <br /> <h3>API POST (data from embedded string)</h3> <span>This example shows how to POST data to API service where data (i.e. request body) is coming from embedded string.</span> <pre class="codeblock"><code class="sql">SELECT * FROM $ limit 3 WITH (METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA' ,SRC='http://httpbin.org/post' ,BODY=' { id:1, notes:"Line1\r\nLine2" }' ) </code></pre> <br /> <h3>API POST - Send/Upload file (data from local file)</h3> <span>This example shows how to POST data to API service where data (i.e. request body) is coming from local file.</span> <pre class="codeblock"><code class="sql">SELECT * FROM $ limit 3 WITH (METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA' ,SRC='http://httpbin.org/post' ,BODY='@c:\files\dump.json' ,IsMultiPart='True' ) </code></pre> <br /> <h3>Call stored proc (Ad-Hoc HTTP request).</h3> <span>This example shows how to fetch data from any http URL via stored procedure call. Using this method you can fetch any data from HTML pages not necessarily JSON.</span> <pre class="codeblock"><code class="sql">exec webRequest 'http://httpbin.org/post', 'POST', '*BODY*', 'x-hdr1: AA||x-hdr2: BB' --You can also arrange parameters in any order using named parameters and optional parameters --exec webRequest @verb='POST', @uri='https://httpbin.org/post', @body='*BODY*', @headers='Insecure-Requests: 0||User-Data: httpbin.org' --exec webRequest @verb='GET', @uri='https://httpbin.org/get' </code></pre> <br /> <h3>Functions - String Manipulation</h3> <span>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.</span> <pre class="codeblock"><code class="sql">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" } ] } }' )</code></pre> <br /> <h3>Functions - Control flow</h3> <span>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.</span> <pre class="codeblock"><code class="sql">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" }] } }')</code></pre> <br /> <h3>Functions - Date/Time</h3> <span>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.<br /><br />JSON driver supports following 5 datetime functions. The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns YYYY-MM-DD HH:MM:SS.<br /><br />date(timestring, modifier, modifier, ...)<br />time(timestring, modifier, modifier, ...)<br />datetime(timestring, modifier, modifier, ...)<br />julianday(timestring, modifier, modifier, ...)<br />strftime(format, timestring, modifier, modifier, ...)<br /><br />=======================================<br />time string (input data)<br />=======================================<br />A time string (input) can be in any of the following formats. You can hard code or supply from column.<br /><br />YYYY-MM-DD<br />YYYY-MM-DD HH:MM<br />YYYY-MM-DD HH:MM:SS<br />YYYY-MM-DD HH:MM:SS.SSS<br />YYYY-MM-DDTHH:MM<br />YYYY-MM-DDTHH:MM:SS<br />YYYY-MM-DDTHH:MM:SS.SSS<br />HH:MM<br />HH:MM:SS<br />HH:MM:SS.SSS<br />now<br />DDDDDDDDDD<br /><br />=======================================<br />format specifier for strftime function<br />=======================================<br />Use below format specifiers to output date in desired format. For example to output date in MM-DD-YYYY use below select query <br /><br />select STRFTIME('%Y-%m-%d','now') formatted_date_time from mytable<br />select STRFTIME('%Y-%m-%d',[my column]) formatted_date_time from mytable<br /><br />%d day of month: 00<br />%f fractional seconds: SS.SSS<br />%H hour: 00-24<br />%j day of year: 001-366<br />%J Julian day number<br />%m month: 01-12<br />%M minute: 00-59<br />%s seconds since 1970-01-01<br />%S seconds: 00-59<br />%w day of week 0-6 with Sunday==0<br />%W week of year: 00-53<br />%Y year: 0000-9999<br />%% %<br /><br />=================<br />Modifiers<br />=================<br />The time string (input) can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.<br /><br />For example to add 5 days in current date you call it as below<br /><br />select DATE('now', '+5 day') from mytable<br /><br />NNN days<br />NNN hours<br />NNN minutes<br />NNN.NNNN seconds<br />NNN months<br />NNN years<br />start of month<br />start of year<br />start of day<br />weekday N<br />unixepoch<br />localtime<br />utc<br /><br />The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.<br /><br />The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.<br /><br />The 'weekday' modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.<br /></span> <pre class="codeblock"><code class="sql">SELECT DATE('now') date_now ,STRFTIME('%Y-%m-%dT%H:%M:%S.%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" } ] } }')</code></pre> <br /> <h3>Functions - Math</h3> <span>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.</span> <pre class="codeblock"><code class="sql">SELECT abs(-500) ,random() ,round(1000.236, 2) FROM $WITH (DATA='{ Info: { Rows: [ { CompanyId: 1000 , CompanyName: "ZappySys" }, { CompanyId: 1001 , CompanyName: "Microsoft" } ] } }')</code></pre> <br /> <h3>Functions - String Manipulation</h3> <span>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.</span> <pre class="codeblock"><code class="sql">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" } ] } }' )</code></pre> <br /> <h3>Functions - JSON</h3> <span>This example shows how to use various JSON functions.</span> <pre class="codeblock"><code class="sql">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'']"}] }') </code></pre> <br /> <h3>Functions - JSON (Flattening un-collapsed JSON)</h3> <span>This example shows how to flatten JSON arrays which are not flattened by the driver. Another way to flatten small arrays without using json_value is set EnableArrayFlattening=true </span> <pre class="codeblock"><code class="sql"> /* SELECT * FROM value WITH(SRC='https://services.odata.org/TripPinRESTierService/People' , EnableArrayFlattening = 'True' --This is another way to extract data from small arrays. It will flatten arrays upto N items , MaxArrayItemsToFlatten = 5 ) */ -- OR -- SELECT UserName, Features as Features_Raw, json_value(Features,'$.[0]') || ',' || json_value(Features,'$.[1]') Features_CSV, AddressInfo as AddressInfo_Raw, json_value(AddressInfo,'$.[0].Address') Address1_Address, json_value(AddressInfo,'$.[0].City.Name') Address1_City, json_value(AddressInfo,'$.[0].City.CountryRegion') Address1_Country, json_value(AddressInfo,'$.[0].City.Region') Address1_Region, json_value(AddressInfo,'$.[1].Address') Address2_Address, json_value(AddressInfo,'$.[1].City.Name') Address2_City, json_value(AddressInfo,'$.[1].City.CountryRegion') Address2_Country, json_value(AddressInfo,'$.[1].City.Region') Address2_Region, Cost FROM value WITH(SRC='https://services.odata.org/TripPinRESTierService/People' ) </code></pre> <br /> <h3>Functions - Regular Expression</h3> <span>This example shows how to regular expression functions.</span> <pre class="codeblock"><code class="sql">SELECT regex_ismatch(SSN, '\d{3}-\d{3}-\d{4}') IsValidSSN, --check pattern found regex_replace(SSN, '\d','*') MaskedSSN, --search/replace pattern regex_search(SSN, '\d{4}') SSNLast4Digits, --extract substring using pattern search regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',0, 2) DomainOf1stEmailByIndex, --extract 1st email (zero based index) and get domain name (by group name). For 2nd argument you can use numeric index or named argument. Both arguments are zero based index. regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',2, 2) DomainOf3rdEmailByIndex, --extract 3rd email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. regex_search('a@x.com | b@y.com | c@z.com', '(?<name>\w+)@(?<domain>\w+.com)','-0', 2) DomainOfLastEmail, --extract last email (use negative sign in quote - zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. regex_search( 'a@x.com | b@y.com | c@z.com' , '(?<name>\w+)@(?<domain>\w+.com)' , '*' --occurrence match index ( * , N or -N ) negative number will do reverse find (e.g. to get last match use -0 , to get second last use -1) , 2 --group index from match (default is full match or -1) --, 0 -- ignore case for pattern match (0=strict match, 1=ignore case, default is 0) --, 0 -- throw error if not matched 0=don't throw error, 1=throw error --, '#' -- separator for multi match ) GetAllDomains --extract domains from all emails and show as email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. FROM $ WITH (Filter='$.data.row[*]', DATA= '{ data: { row:[{SSN: "111-222-4444", Email: "a@x.com | b@y.com | c@z.com" },{SSN: "111-X2Z-AB44", Email: "a@x.com | b@y.com | c@z.com"}] } } ')</code></pre> <br /> <h3>Query using filter option</h3> <span>This example shows how to query data using direct Filter expression and some other filter related settings.</span> <pre class="codeblock"><code class="sql">SELECT * FROM $ WITH ( --enter path in SRC or use static value in DATA' --SRC='http://some/api/data.json' --SRC='C:\somefile.json' --SRC='C:\some*.json' DATA=' { version:1, doc: { location: {lat:111.55,lon:-222.12}, rows: [ {id:1, name: "AAA"}, {id:2, name: "BBB"}, {id:3, name: "CCC"} ] } }' ,Filter='$.doc.rows[*]' -- for more information check this https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet/ ,IncludeParentColumns='true' --this will include any columns found above filtered node (e.g. rows). ,ParentColumnPrefix='p_' )</code></pre> <br /> <h3>Pivot Value (Columns to Rows)</h3> <span>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.</span> <pre class="codeblock"><code class="sql">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='http://some/api/data.json' --SRC='C:\some*.json' --SRC='C:\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' )</code></pre> <br /> <h3>Query 2D Array (Simple Transformation)</h3> <span>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[*])</span> <pre class="codeblock"><code class="sql">SELECT * FROM rows WITH( --enter path in SRC or use static value in DATA' --SRC='http://some/api/data.json' --SRC='C:\some*.json' --SRC='C:\somefile.json' DATA=' { "columns": ["RecordID","CustomerID","CustomerName"], "rows": [ [1,"AAA","Customer A"], [2,"BBB","Customer B"], [3,"CCC","Customer C"] ] }' ,ArrayTransformType='TransformSimpleTwoDimensionalArray' ,ArrayTransColumnNameFilter='$.columns[*]' )</code></pre> <br /> <h3>Query 2D Array (Simple Transformation with Missing Columns)</h3> <span>This example shows how to query compact 2D array (array inside array) where column name is missing.</span> <pre class="codeblock"><code class="sql">SELECT * FROM rows WITH( --enter path in SRC or use static value in DATA' --SRC='http://some/api/data.json' --SRC='C:\some*.json' --SRC='C:\somefile.json' DATA=' { "rows": [ [1,"AAA","Customer A"], [2,"BBB","Customer B"], [3,"CCC","Customer C"] ] }' ,ArrayTransformType='TransformSimpleTwoDimensionalArray' ,ArrayTransEnableCustomColumns='True' ,ArrayTransCustomColumns='RecordID,CustomerID,CustomerName' )</code></pre> <br /> <h3>Query 2D Array (Complex Transformation)</h3> <span>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[*])</span> <pre class="codeblock"><code class="sql">SELECT * FROM rows WITH( --enter path in SRC or use static value in DATA' --SRC='http://some/api/data.json' --SRC='C:\some*.json' --SRC='C:\somefile.json' DATA=' { requestid:1, schema:{ fields:[{name:"id"},{name:"name"},{name:"birthdate"}] }, rows: [ { f:[{v:10},{v:"aaa"},{v:"2015-01-01"}]}, { f:[{v:20},{v:"bbb"},{v:"2015-02-01"}] } ] }' ,ArrayTransformType = 'TransformComplexTwoDimensionalArray' ,ArrayTransColumnNameFilter = '$.schema.fields[*].name' ,ArrayTransRowValueFilter = '$.f[*].v' )</code></pre> <br /> <h3>Query API with Status Check Loop (Wait until value found)</h3> <span>This example shows how to use status check feature to keep waiting until desired value is not found in response. This pattern is very useful for Job style API where you have to wait until API returns Success or Failure status of Job status. You can adjust iteration wait time, max wait time for timeout and regex pattern to search for Success or Failure. If Success value found it returns data. If Failure value found or timeout occurs it will throw error. <br /><br />Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/<br />Examples of JsonPath: https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet/<br />Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/<br /><br /></span> <pre class="codeblock"><code class="sql">SELECT * FROM $ WITH ( SRC='https://zappysys.com/downloads/files/test/cust-1.json', EnableStatusCheck='True', StatucCheckMaxWaitSeconds=7, StatucCheckIterationWaitSeconds=3, --StatusCheckForFailedValue = 'True', --StatusFailedValue = 'Failed|Cancelled', --StatusFieldFilterExpr='$.response.status', --Use XPath, JsonPath or Regex to narrow down to single property / content under which you like to search --StatusFieldFilterType='Json', --or use Xml, Regex, None StatusSuccessValue='C1' --Wait until C1 or C2 or C3 found in response (Use Regular expression syntax) ) /* --- See real world example of status check pattern here (Use of XML Driver)... https://zappysys.com/blog/import-bing-ads-data-sql-server-performance-reports/ SELECT c.* FROM $ WITH( --/////////////////////////////////// --Step-1: submit report request for XML format --/////////////////////////////////// alias='a' ,Src='https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,Filter='$.s:Envelope.s:Body.SubmitGenerateReportResponse' ,RequestData='@C:\Requests\BingAds\sql_body1.xml' --Get Body from File for clean Look (Read blog post above to see actual body) ,IsMultiPart='True' ,RequestContentTypeCode='TextXml' ,Header='SOAPAction: "SubmitGenerateReport"' ,RequestMethod = 'POST' ,EnableBodyPlaceholderForMultiPart = 'True' ,Meta = '[{ "Name": "ReportRequestId","Type": "Int64"}]' --/////////////////////////////////// --///Step-2: keep checking status until report is ready - returns final url when done --/////////////////////////////////// ,join1_alias = 'b' ,join1_Filter = '$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus' ,join1_RequestData = '@C:\Requests\BingAds\sql_body2.xml' ,join1_IsMultiPart = 'True' ,join1_Src = 'https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,join1_RequestContentTypeCode = 'TextXml' ,join1_Header = 'SOAPAction: "PollGenerateReport"' ,join1_RequestMethod = 'POST' ,join1_EnableBodyPlaceholderForMultiPart = 'True' ,join1_EnableStatusCheck = 'True', ,join1_StatucCheckMaxWaitSeconds = 300, --wait max 5 mins ? ,join1_StatucCheckIterationWaitSeconds = 5, --check every 5 sec ? ,join1_StatusSuccessValue = 'Success', --look for success word in response --, join1_Meta = '[{ "Name": "ReportDownloadUrl","Type": "String",Length: "500"},{ "Name": "Status","Type": "String",Length: "20"}]' --/////////////////////////////////// --Step-3: Download report(Zip file) and Parse rows --/////////////////////////////////// ,join2_alias = 'c' ,join2_DataConnectionType = 'Default' ,join2_Src = '[$b.ReportDownloadUrl$]' ,join2_Filter = '$.Report.Table.Row[*]' ,join2_ElementsToTreatAsArray = 'Row' ,join2_RequestMethod = 'GET' ,join2_IsMultiPart = 'False' ,join2_FileCompressionType = 'Zip' ) */ </code></pre> <br /> <h3>Language - Group By / Limit / Order By</h3> <span>This example shows how to use GROUP BY / ORDER BY and LIMIT clauses.</span> <pre class="codeblock"><code class="sql">SELECT Country AS Invoice_Country , SUM(UnitPrice * Quantity) AS Invoice_Total FROM value WHERE Discount > 0 GROUP BY Country HAVING SUM(UnitPrice * Quantity) > 1000 ORDER BY Invoice_Total DESC LIMIT 3 WITH (SRC='http://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json') </code></pre> <br /> <h3>Language - Case Statement</h3> <span>This example shows how to write CASE statement to select / output different value based on multiple conditions.</span> <pre class="codeblock"><code class="sql">SELECT name , CASE id WHEN 1 THEN 1+1 WHEN 2 THEN 2+2 ELSE 0 END ThisIsCaseColumn FROM rows WITH (DATA=' { rows : [ {id:1, name: "AAA"}, {id:2, name: "BBB"}, {id:3, name: "CCC"} ] }' )</code></pre> <br /> <h3>Language - Basic SQL queries</h3> <span>Basic query examples.</span> <pre class="codeblock"><code class="sql"> --Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct. --Line that starts with two dashes is a comment and wont be executed --Any query must contain at least two required parts --data source (or table) names and a list of column names --column names come first (preceded by SELECT keyword) --table names follow (preceded by FROM keyword) --Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table SELECT * FROM Accounts --query with compound column and table names SELECT "Account ID" FROM "Closed Accounts" SELECT [Account ID] FROM [Closed Accounts] --returns all rows from 'Accounts' table, each row will have Id and Amount columns SELECT Id, Amount FROM Accounts --returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0 SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0 --returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123 SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123) --returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202 SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202) --returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100 SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100 --returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John' SELECT * FROM Person WHERE Name LIKE 'John%' --case statement SELECT FirstName, LastName, CASE Country WHEN 'US' THEN 'NA' WHEN 'Canada' THEN 'NA' WHEN 'India' THEN 'Asia' WHEN 'China' THEN 'Asia' ELSE 'Unknown' END ColRegion FROM Person </code></pre> <br /> <!--{{FOOTER-CONTENT}}--> </p> <hr> <font face="Arial" color="#808080" size="1">Copyrights reserved. ZappySys LLC.</font></div> <script type="text/javascript"> var $zoho= $zoho || {salesiq:{values:{},ready:function(){}}};var d=document;s=d.createElement("script");s.type="text/javascript"; s.defer=true;s.src="https://salesiq.zoho.com/zappysys/float.ls?embedname=zappysys"; t=d.getElementsByTagName("script")[0];t.parentNode.insertBefore(s,t); $zoho.salesiq.ready=function(embedinfo){$zoho.salesiq.floatbutton.visible("hide");} </script> <script type='text/javascript' src='https://crm.zoho.com/crm/javascript/zcga.js'></script> </body> </html>