Extract json from text using SIddhi Execution Json API

Question

I am trying to extract a valid json from text using Siddhi json Execution API. I have downloaded siddhi-execution-json-1.1.1.jar from wso2 store and following the example mentioned in the documentation there. But the same syntax above is not giving error of "Syntax error in SiddhiiQl, mismatched input 'input' expecting {',',GROUP,ORDER,LIMIT....}" . Below is my synatx:

@info(name='query_name') 
from transact#window.length(1)
select json:group("json",true) as groupedJSONArray
input transact2;

I am using the below text from transact stream :

data: "" {
    "_id": {
        "$oid": "fr4wfwe4"
    },
    "code": "fesfsce",
    "name": "NAME1",
    "desc": "DECRIPTION",
    "transRefId": "FESFCSEFCS",
    "amount": 1000,
    "currency": "USD",
    "sender": {
        "id": "FRESGVSVDVEFE2333",
        "name": "rose",
        "phone": "123456789"
    },
    "message": "",
    "lockedBy": {},
    "activatedBy": {},
    "statusChangedAt": "",
    "linkBankTrans": null,
    "devGrp": 0,
    "requestId": "",
    "codeStatus": null,
    "codeTransRefId": null,
    "extOriginTransId": null
}
""

For reference , i am generating transact stream via below query:

@info(name = 'clean payload with replaceall')
from transactionstream1 
select str:replaceAll(payload,"\\","") as data
insert into transact;

I want to extract the valid json inside data:" " in WSO2 stream processor. Is there some other extension i should use or there is some error in the way i am executing? I need this query above: @info(name='query_name') to work to get json from the above text.

Niveathika · Accepted Answer · 2019-12-12 04:47:25Z

1

Few things to note here,

You are trying to use json:group() function, however, from API docs, this is not supported for v1.1.1, this is the reason for the syntax error. json:group() is an aggregate function, let's say I want to combine JSON elements to a single json every 3 events, in that case, group() can be used but not in your case. json:group() is only available in 2.x.x versions of the siddhi-execution-json, which is NOT compatible with WSO2 Stream Processor. It is compatible with siddhi runner (A fully open source offering which is focused on giving cloud-native abilities to siddhi lib natively ). The next-gen of wso2 sp.
From your previous question, it seemed you have extracted the json string inside the payload element. However, this is in string type and you need it as a JSON object to manipulate with siddhi-execution-json, that's your reason to strip backslashes using clean payload with replace all query. However, you can achieve this by using json:toObject
```
from transactionstream1
select json:toObject(payload) as payloadJson  
insert into transact;
```
For this, we will use Siddhi object data type which can contain any object to pass between queries or manipulate using extensions. Also json:toObject() is a function extension type which is used to transform attributes in one event.

After transforming the string to json object, you can use getString()/getFloat/getBool() functions to extract values from the json object

from transact 
select json:getString(payloadJson, '$.code') as code, 
json:getString(payloadJson, '$.name') as name
insert into LogStream;

BTW, is this MongoDB changes retrieved through Debezium?

edited Dec 12, 2019 at 4:47

answered Dec 12, 2019 at 4:28

Niveathika

1,4472 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Rahul Anand Over a year ago

Yes, MongoDB changes retrieved through Debezium

Rahul Anand Over a year ago

One more issue i am facing is , when i restart my wso2sp i start getting below error with regex, which wasn't there earlier: "exception occurred when converting text message : java.lajava.lang.StackOverflowError"

Niveathika Over a year ago

Siddhi does have a CDC source, however, as of now, it doesn't support MongoDB. You can contribute to siddhi-io-cdc, then you can straightaway get the payload. github.com/siddhi-io/siddhi-io-cdc/blob/siddhi-4.x.x/component/… is the place to add configs

Rahul Anand Over a year ago

can we shift to chat , stack trace is too long , won't adjust in comments? I don't know how to enable state persistence in wso2sp?

Rahul Anand Over a year ago

After using json:toObject(payload) i want to extract entire valid json inside payload

|

Collectives™ on Stack Overflow

Extract json from text using SIddhi Execution Json API

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related