Parse a JSON value from String in the Workflow Query

[RadAndric]

Hi All,

Is there a way to parse JSON string coming from Query activity in technical workflow without creating Java Script Activity? Java Script Activity is limited to a number of records it can process (I think 10,000) - so I am trying to determine if there is a function or expression that can be used to get JSON element value as part of Query activity or some other way that is not limited to number of records it can process.

[Marcel_Szimonisz]

Hello @RadAndric ,

the query activities can use all the SQL functions that you database engine has. So if let say if you use PostgreeSQL you can parse it

https://stackoverflow.com/questions/32626261/how-to-parse-json-in-postgresql
I would use SQL activity and use example in the above link to parse it into separate columns

Marcel

[CedricRey]

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 . 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

[Parvesh_Parmar · Accepted solution]

Hello @RadAndric ,

You can create a function to pare the  JSON data and get the result with query activity. 

For e.g you have data like below in a table

id	jsonData
1	{"name":"John", "age":30, "car":"BMW"}'

In the query activity you need to get the name, age and car, like below. 

There you need to create a three function like nameFun, ageFunc and carFun to get the value of name, age and car respectively. 

Function definition depends upon your database.  if you are using PostGreeSql data base, then you the syntax below. This is a just a dummy example just  for information. You might be need to update it according to you. 

<?xml version="1.0" encoding='ISO-8859-1' ?>
<!-- ===========================================================================
Additional SQL functions for Adobe Campaign
========================================================================== -->
<package
namespace = "nms"
name = "package-additional-funclist"
label = "Additional functions"
buildVersion= "6.1"
buildNumber = "10000">

<entities schema="xtk:funcList">
<funcList name="myList" namespace="cus">
<group name="date" label="nameFun">
<function name="nameFun" type="string" args="(<jsonData>)"
minArgs="1" maxArgs="1" >
<providerPart provider="PostgreSQL" body="($1)->'name'"/>
</function>
</group>
</funcList>
</entities>
</package>

Thanks.

Parvesh.

[Sukrity_Wadhwa]

Hi @RadAndric,

Were you able to resolve this query with the help of the given solutions or do you still need more help here? Do let us know.
In case the given solutions were helpful, then kindly choose the one that helped you the most as the 'Correct Reply'.
Thanks!