Product: Kryon RPA
Product Version:19.1
Components: Database; Kryon Studio
Article Contributors: Zakir Khan
Purpose of creating this article: 1- Kryon Execute SQL query AC can run Queries with JSON functions (e.g. JSON_Extract ) functions 2- To showcase how to run / check it step by step 3- For this example using MYSQL but same can be achieved with MSSQL
Prerequisites:1- Kryon Studio v19.1 or above 2- MySQL Workbench to create DB/Table 3- Access to database
Steps:
Set up Table with JSON parameter using MySQL workbench:
Set up Table with JSON parameter using MySQL workbench:
Note: Click this Link to download MySQL Workbench Please find below full script:
- Create the table tj10 :
Use rpadb;CREATE TABLE tj10 (a JSON, b INT);INSERT INTO tj10 VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);INSERT INTO tj10 VALUES ('[3,10,5,"x",44]', 33), ('[3,10,5,17,[22,"y",66]]', 0);- Run the following query to check the table as shown below :
SELECT * FROM rpadb.tj10;
Getting an element from Nested Array:
Note: Table we created having two column "a" and "b". Column "a" is of nested array type SELECT JSON_UNQUOTE(JSON_EXTRACT(a, "$[4][1]")) FROM rpadb.tj10;There is one more way but this doesn't works with Kryon Studio:
SELECT a->"$[4][1]" FROM rpadb.tj10;
Action to be performed in Kryon Studio:
- Open Studio and create a new blank wizard
- Get Execute SQL AC and setup the query string
- If you already having query string the paste in required connection string field or else use Builder option (See pic3)
- If using Builder then select appropriate driver and select appropriate DSN (Data source name) created for MySQL server instance using ODBC data source 64-bit. And check if connection is successful. (See pic 4 , 5, 6 & 7))
Info for Pic7: Search for ODBC Data source 64-bit in Start search menu and then add system data source under System DSN tab. Same you will be able to see in Kryon studio under connection string builderPic 7:
Final Query Inside Kryon Studio:
Note: Make sure to replace "$" sign from MySql query with a variable inside Kryon Studio. - Final query to be written in SQL AC:
SELECT JSON_UNQUOTE(JSON_EXTRACT(a, "$dollar$[4][1]")) as name FROM rpadb.tj10;
See the following output:
More Information:https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html