Avatar

Community Advisor

Hi @RadAndric , Depending of your database engine, you can add your custom xtk function by importing a package with a funcList entitiy :

<?xml version='1.0'?>
<package author="RadAndric" buildDate="2022-05-11 08:54:24.101Z" buildNumber="9032" buildVersion="6.7">
  <entities schema="xtk:funcList">
    <funcList entitySchema="xtk:funcList" label="My custom function" name="cusFunc" namespace="cus">       
      <group label="Custom functions" name="custom">          
        <function args="(&lt;String&gt;,&lt;String&gt;)" display="extract value $2 in JSON of the $1 data field" help="Describe your function"
                  maxArgs="2" minArgs="2" name="JSONValue" type="long">
          <providerPart body="SQLCODE_TO_EXTRACT_JSONVALUE($1,$2)"
                        provider="YOURENGINE"/>
        </function>
      </group>
    </funcList>
  </entities>
</package>
//YOURENGINE can be PostgreSQL,RedShift,Oracle,SybaseIQ,MySQL,DB2,Teradata etc.
//SQLCODE must be the SQL expression to extract the JSON, using the argument with $X for placing argument

You'll have to disconnect / reconnect your console to see your new function in every query assistant functions list (the 'Edit formula with expression part').

Got exactly this function working on workflows with a MSSQL server (named it JSONValue), but no luck on a Oracle DB engine :disappointed_face: . Also, the function works when using it in a workflow query activity, but don't remember why, it failed when I try to use it with the console.

If your engine provide builtin and simple function that could make this extract, you could try to implement it