You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by siva <si...@bizruntime.com> on 2020/12/17 17:12:18 UTC
How to extracts value from json string in .Net Ignite
Hi All,
I have .Net ClientServer Ignite Application.And Table Model annotation with
QuerySQLField.
In Tables some of column contains json string.
How to extracts value from json string field in query?
*for example query*
select uuid, json_value(Payload,'$.comments') as comments from
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".dailyjournal;
Could you please guide me how to approach this.
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Hi,
But i am facing same issue.
not sure missing any configuration?
1.added jar file in libs folder
2.added function in default config at ignite installation config directory.
3.added function in both client and server nodes xml file.
I have restarted my application .Net client and server nodes.
0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> select
Custom_Json_Value("","") foo;
Ignite console log:
[2020-12-21
20:55:33,918][ERROR][client-connector-#160%ServerNode%][JdbcRequestHandler]
Failed to execute SQL query [reqId=0, req=JdbcQueryExecuteRequest
[schemaName=MYTEMPLATEFUNCTION, pageSize=1024, maxRows=0, sqlQry=select
Custom_Json_Value("","") foo, args=[], stmtType=ANY_STATEMENT_TYPE,
autoCommit=true]]
class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed
to set schema for DB connection for thread [schema=MYTEMPLATEFUNCTION]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:587)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForSchema(IgniteH2Indexing.java:414)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:2179)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2128)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2123)
at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2693)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2137)
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:511)
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.doHandle(JdbcRequestHandler.java:245)
at
org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:208)
at
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:162)
at
org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:45)
at
org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279)
at
org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109)
at
org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:120)
at
org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLException: Schema "MYTEMPLATEFUNCTION" not
found; SQL statement:
SET SCHEMA "MYTEMPLATEFUNCTION" [90079-197]
at
org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.engine.Database.getSchema(Database.java:1808)
at org.h2.command.dml.Set.update(Set.java:409)
at org.h2.command.CommandContainer.update(CommandContainer.java:102)
at org.h2.command.Command.executeUpdate(Command.java:261)
at
org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:169)
at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:579)
... 20 more
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Yes, it should work like that. Templates don't do anything until you create
a cache with them. You can also create a simple empty table instead of
putting it in a template.
Regards,
--
Ilya Kasnacheev
чт, 24 дек. 2020 г. в 12:03, siva <si...@bizruntime.com>:
> Hi,
> Thank you all for help.
> created function and deployed in .net nodes.
>
> But i am not getting why Function is working only after creating at least
> on
> one table with
> cache template of function class.
>
> is this should work like that? or something i missing?
>
> Thanks.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Hi,
Thank you all for help.
created function and deployed in .net nodes.
But i am not getting why Function is working only after creating at least on
one table with
cache template of function class.
is this should work like that? or something i missing?
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by JP <jo...@gmail.com>.
Hello,
This link might help you,
http://apache-ignite-users.70518.x6.nabble.com/ignite-net-custom-sql-functions-td27190.html
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Thank you so much for help .
After running by exe and passing arguments it is working in .Net server.
But Function is working after creating at least on one table with cache
template of function.is this should work like that?
I am not sure but
If you don't mind just for clarification Actually i want apply that custom
function cache template in all caches using code.How to apply same function
cache template on this below cache config?
var cacheCfg = new CacheConfiguration("MyTemplate*")
{
Name = "MyTemplate",
CacheStoreFactory = new
ConstructionTenantCacheStoreFactory(_logger, connectionString),
KeepBinaryInStore = false, // Cache store works with
deserialized data.
ReadThrough = true,
WriteThrough = true,
WriteBehindEnabled = true,
QueryEntities = queryList,
WriteBehindFlushThreadCount = 2,
CacheMode = CacheMode.Partitioned,
Backups = backupNodes,
DataRegionName = "IgniteDataRegion",
EvictionPolicy = new LruEvictionPolicy
{
MaxSize = 100000
},
EnableStatistics = cacheMetrics,
WriteSynchronizationMode =
CacheWriteSynchronizationMode.FullSync,
GroupName=groupName
};
var cache = Ignite.GetOrCreateCache<string,object>(cacheCfg);
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I think you need to specify your JAR file with the -JvmClasspath argument
of ignite.exe or corresponding IgniteConfiguration class property.
Regards,
--
Ilya Kasnacheev
вт, 22 дек. 2020 г. в 11:03, siva <si...@bizruntime.com>:
> Hi,
> I have added custom function jar file and added cache configuration in
> default config xml and started server using ignite.bat.I am able to query
> my
> custom function.
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo;
> +--------------------------------+
> | FOO |
> +--------------------------------+
> | Test |
> +--------------------------------+
> 1 row selected (0.034 seconds)
> 0: jdbc:ignite:thin://127.0.0.1/>
>
> But in .NET Server case it's not working
> IgniteHome,cacheconfig and libs there in at ignite binary.
> added same cache configuration in .net server xml file.
>
> i have tested create command as well with same cache template but it's not
> working.what might be the cause?
>
> 0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo;
> Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL
> statement:
> SELECT Custom_Json_Value('','') foo [90022-197] (state=42000,code=1001)
> java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE"
> not found; SQL statement:
> SELECT Custom_Json_Value('','') foo [90022-197]
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> help is very appreciate.thanks.
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Hi,
I have added custom function jar file and added cache configuration in
default config xml and started server using ignite.bat.I am able to query my
custom function.
0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo;
+--------------------------------+
| FOO |
+--------------------------------+
| Test |
+--------------------------------+
1 row selected (0.034 seconds)
0: jdbc:ignite:thin://127.0.0.1/>
But in .NET Server case it's not working
IgniteHome,cacheconfig and libs there in at ignite binary.
added same cache configuration in .net server xml file.
i have tested create command as well with same cache template but it's not
working.what might be the cause?
0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo;
Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL
statement:
SELECT Custom_Json_Value('','') foo [90022-197] (state=42000,code=1001)
java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE"
not found; SQL statement:
SELECT Custom_Json_Value('','') foo [90022-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
help is very appreciate.thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Seems to work for me:
~/Downloads/apache-ignite-slim-2.9.0-bin% mkdir -p org/mycustomsqlfunction
~/Downloads/apache-ignite-slim-2.9.0-bin% cd org/mycustomsqlfunction
~/Downloads/apache-ignite-slim-2.9.0-bin/org/mycustomsqlfunction% cat >
MyCustomSQLFunction.java
package org.mycustomsqlfunction;
import org.apache.ignite.cache.query.annotations.QuerySqlFunction;
public class MyCustomSQLFunction {
@QuerySqlFunction
public static String Custom_Json_Value(String payload,String fieldName)
{
System.out.println(payload);
System.out.println(fieldName);
return "Test";
}
}%
~/Downloads/apache-ignite-slim-2.9.0-bin/org/mycustomsqlfunction% cd ../..
~/Downloads/apache-ignite-slim-2.9.0-bin% javac -cp
libs/ignite-core-2.9.0.jar:libs/ignite-indexing/ignite-indexing-2.9.0.jar:libs/ignite-indexing/h2-1.4.197.jar
org/mycustomsqlfunction/MyCustomSQLFunction.java
~/Downloads/apache-ignite-slim-2.9.0-bin% jar vcf
libs/mycustomsqlfunction.jar
org/mycustomsqlfunction/MyCustomSQLFunction.class
org/mycustomsqlfunction/MyCustomSQLFunction.class
~/Downloads/apache-ignite-slim-2.9.0-bin% vim config/default-config.xml
...
~/Downloads/apache-ignite-slim-2.9.0-bin% javac -cp
libs/ignite-core-2.9.0.jar:libs/ignite-indexing/ignite-indexing-2.9.0.jar:libs/ignite-indexing/h2-1.4.197.jar
org/mycustomsqlfunction/MyCustomSQLFunction.java
(130)~/Downloads/apache-ignite-slim-2.9.0-bin% bin/ignite.sh
....
~/Downloads/apache-ignite-slim-2.9.0-bin% bin/sqlline.sh
0: jdbc:ignite:thin://localhost/MyTemplateFun> !connect
jdbc:ignite:thin://localhost/"MyTemplateFunction"
Enter username for jdbc:ignite:thin://localhost/"MyTemplateFunction":
Enter password for jdbc:ignite:thin://localhost/"MyTemplateFunction":
1: jdbc:ignite:thin://localhost/"MyTemplateFu> select
custom_json_value('','') foo;
+--------------------------------+
| FOO |
+--------------------------------+
| Test |
+--------------------------------+
1 row selected (0,108 seconds)
Regards,
--
Ilya Kasnacheev
пн, 21 дек. 2020 г. в 17:39, siva <si...@bizruntime.com>:
> Hi,
>
>
> i have tried like below in both case i am getting error
>
> 1.Without cache name
>
> 0: jdbc:ignite:thin://127.0.0.1/> select Custom_Json_Value("","");
> Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL
> statement:
> select Custom_Json_Value("","") [90022-197] (state=42000,code=1001)
> java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE"
> not found; SQL statement:
> select Custom_Json_Value("","") [90022-197]
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
>
> 2.i logged in with customfunction cache name 'MyTemplateFunction'
>
> select Custom_Json_Value("","");
>
> 0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> select
> Custom_Json_Value("","");
> Error: Failed to set schema for DB connection for thread
> [schema=MYTEMPLATEFUNCTION] (state=50000,code=1)
> java.sql.SQLException: Failed to set schema for DB connection for thread
> [schema=MYTEMPLATEFUNCTION]
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun>
>
>
> Thanks.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Hi,
i have tried like below in both case i am getting error
1.Without cache name
0: jdbc:ignite:thin://127.0.0.1/> select Custom_Json_Value("","");
Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL
statement:
select Custom_Json_Value("","") [90022-197] (state=42000,code=1001)
java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE"
not found; SQL statement:
select Custom_Json_Value("","") [90022-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
2.i logged in with customfunction cache name 'MyTemplateFunction'
select Custom_Json_Value("","");
0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> select
Custom_Json_Value("","");
Error: Failed to set schema for DB connection for thread
[schema=MYTEMPLATEFUNCTION] (state=50000,code=1)
java.sql.SQLException: Failed to set schema for DB connection for thread
[schema=MYTEMPLATEFUNCTION]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun>
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I don't think that you need to qualify functions with schema name. Please
try it verbatim. You can specify default schema in the connection string,
or alternatively try to do actual select from that table:
https://ignite.apache.org/docs/latest/SQL/JDBC/jdbc-driver
Regards,
--
Ilya Kasnacheev
пн, 21 дек. 2020 г. в 15:40, siva <si...@bizruntime.com>:
> Hi,
> I have created and defined customsqlfunction
> IGNITE_HOME=apache-ignite-2.7.6-bin
> added Function jar at apache-ignite-2.7.6-bin\libs
>
> And added cache configuration in default-config
> <bean id="grid.cfg"
> class="org.apache.ignite.configuration.IgniteConfiguration">
> <property name="cacheConfiguration">
> <list>
> <bean class="org.apache.ignite.configuration.CacheConfiguration">
> <property name="name" value="MyTemplateFunction"/>
> <property name="cacheMode" value="PARTITIONED" />
> <property name="backups" value="1" />
> <property name="sqlFunctionClasses"
> value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
> </bean>
> </list>
> </property>
> </bean>
>
> And also added in .Net client and Server config files node like this
> <property name="cacheConfiguration">
> <list>
> <bean class="org.apache.ignite.configuration.CacheConfiguration">
> <property name="name" value="MyTemplateFunction"/>
> <property name="cacheMode" value="PARTITIONED" />
> <property name="backups" value="1" />
> <property name="sqlFunctionClasses"
> value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
> </bean>
> </list>
> </property>
>
> *Java class file*
> package org.mycustomsqlfunction;
>
> import org.apache.ignite.cache.query.annotations.QuerySqlFunction;
>
> public class MyCustomSQLFunction {
> @QuerySqlFunction
> public static String Custom_Json_Value(String payload,String fieldName)
> {
> System.out.println(payload);
> System.out.println(fieldName);
> return "Test";
> }
> }
>
> But while running query getting error column not found:
>
> 0: jdbc:ignite:thin://127.0.0.1/> select
> "MyTemplateFunction".Custom_Json_Value("","");
> Error: Failed to parse query. Column not found; SQL statement:
> select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
> (state=42000,code=1001)
> java.sql.SQLException: Failed to parse query. Column not found; SQL
> statement:
> select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
> at
>
> org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
> at sqlline.Commands.execute(Commands.java:823)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:ignite:thin://127.0.0.1/>
>
> what am i doing wrong.please help.
> Thanks.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: How to extracts value from json string in .Net Ignite
Posted by siva <si...@bizruntime.com>.
Hi,
I have created and defined customsqlfunction
IGNITE_HOME=apache-ignite-2.7.6-bin
added Function jar at apache-ignite-2.7.6-bin\libs
And added cache configuration in default-config
<bean id="grid.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="MyTemplateFunction"/>
<property name="cacheMode" value="PARTITIONED" />
<property name="backups" value="1" />
<property name="sqlFunctionClasses"
value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
</bean>
</list>
</property>
</bean>
And also added in .Net client and Server config files node like this
<property name="cacheConfiguration">
<list>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="MyTemplateFunction"/>
<property name="cacheMode" value="PARTITIONED" />
<property name="backups" value="1" />
<property name="sqlFunctionClasses"
value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
</bean>
</list>
</property>
*Java class file*
package org.mycustomsqlfunction;
import org.apache.ignite.cache.query.annotations.QuerySqlFunction;
public class MyCustomSQLFunction {
@QuerySqlFunction
public static String Custom_Json_Value(String payload,String fieldName)
{
System.out.println(payload);
System.out.println(fieldName);
return "Test";
}
}
But while running query getting error column not found:
0: jdbc:ignite:thin://127.0.0.1/> select
"MyTemplateFunction".Custom_Json_Value("","");
Error: Failed to parse query. Column not found; SQL statement:
select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
(state=42000,code=1001)
java.sql.SQLException: Failed to parse query. Column not found; SQL
statement:
select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/>
what am i doing wrong.please help.
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to extracts value from json string in .Net Ignite
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
You can define your own SQL function json_value to do that:
https://ignite.apache.org/docs/latest/SQL/custom-sql-func
You will need to do that in Java and deploy it to your server nodes (if
you're using client node as a gateway for thin clients, also to such client
nodes).
Regards,
--
Ilya Kasnacheev
чт, 17 дек. 2020 г. в 20:14, siva <si...@bizruntime.com>:
> Hi All,
> I have .Net ClientServer Ignite Application.And Table Model annotation with
> QuerySQLField.
> In Tables some of column contains json string.
>
> How to extracts value from json string field in query?
>
> *for example query*
> select uuid, json_value(Payload,'$.comments') as comments from
>
> "F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".dailyjournal;
>
> Could you please guide me how to approach this.
>
>
> Thanks.
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>