Level 2

Solved

Parse a JSON value from String in the Workflow Query

Forum|Forum|3 years ago
May 10, 2022
4 replies
2746 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Parvesh_Parmar

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' ?>

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

Marcel_Szimonisz

Community Advisor

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

Level 5

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

Community Advisor

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' ?>

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

Parvesh Parmar – Adobe Community Advisor https://www.linkedin.com/in/parvesh-parmar/

Sukrity_Wadhwa

Community Manager

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!

Sukrity Wadhwa

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded