Db2 Jupyter Notebook Macros

In [ ]:

    

%%sql macro j2b
  if {argc} != 1
     echo Syntax: %j2b(value)
     exit
  endif
SYSTOOLS.JSON2BSON({1})


    

In [ ]:

    

%%sql macro b2j
   if {argc} != 1
      echo Syntax: %b2j(value)
      exit
   endif
SYSTOOLS.BSON2JSON({1})


    

In [ ]:

    

%%sql macro js
   if {argc} = 3
       TRIM(JSON_VAL({1},'{2}','s:{3}'))
   else
       echo Syntax: #js( json_column, field ,column_size] )
       exit
   endif


    

In [ ]:

    

%%sql macro ji
   if {argc} = 2
       JSON_VAL({1},'{2}','i')
   else  
       echo Syntax: #ji( [json_column,] field )
       exit
   endif


    

In [ ]:

    

%%sql macro jd
   if {argc} = 3
       CAST (JSON_VAL({1},'{2}','n') AS DECIMAL({3}))
   else
       echo Syntax: #jd( json_column, field ,'precision,scale')
       exit
   endif


    

In [ ]:

    

%%sql macro jdate
   if {argc} = 2  
      CAST (JSON_VAL({1},'{2}','s:10') AS DATE)
   else
       echo Syntax: #jdate( [json_column,] field )
       exit
   endif


    

In [ ]:

    

%%sql macro jsonarray 
if {argc} < 4
    echo Syntax: jsonarray(table_name, pk, json_column, array [,where="optional where clause"])
    exit
endif
var table {1}
var pk {2}
var js_column {3}
var array {4}

jsonarray(pk,item) as 
   (
    select {pk},systools.json2bson(items.value) 
      from {table}, 
        table( systools.json_table({js_column},'{array}','s:2048') ) as items
    if {where} <> null
      where {where} 
    endif  
   )