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 · Answer 1 · 2019-12-12T04:47:25.003

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 '19 at 04:47

answered Dec 12 '19 at 04:28

Niveathika

1,319
2
8
17

Yes, MongoDB changes retrieved through Debezium – Rahul Anand Dec 12 '19 at 04:50
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" – Rahul Anand Dec 12 '19 at 04:51
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. https://github.com/siddhi-io/siddhi-io-cdc/blob/siddhi-4.x.x/component/src/main/java/org/wso2/extension/siddhi/io/cdc/util/CDCSourceUtil.java#L53 is the place to add configs – Niveathika Dec 12 '19 at 04:52
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 Dec 12 '19 at 04:58
After using json:toObject(payload) i want to extract entire valid json inside payload – Rahul Anand Dec 12 '19 at 05:12
sure, if it is siddhi related queries you can also use our slack channel, https://siddhi.io/community/ – Niveathika Dec 12 '19 at 05:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204098/discussion-between-rahul-anand-and-niveathika). – Rahul Anand Dec 12 '19 at 05:17
i have sent the stack trace in chat – Rahul Anand Dec 12 '19 at 05:28
You mean regex.*A="""{.*}""" for example – Rahul Anand Dec 12 '19 at 07:54

Extract json from text using SIddhi Execution Json API

1 Answers1