How to invoke json_val function in db2 json?

Question

I'm trying to index a particular value from stored DB2 nosql, for that I'm using json_val but I need to invoke UDF SYSTOOLS.BSON2JSON to the json_val and need to point the table from where I required data.

SELECT SYSTOOLS.BSON2JSON(CUSTOMERDATA)
FROM  JSONPO
WHERE YEAR(JSON_VAL(CUSTOMERDATA,'PO.@orderDate', 'd')) = 2017;

The above query resulting in sql error -440 so I need to invoke the json_val. How would I invoke it? How would I write stored procedure for it?

The -440 error means that DB2 can't find a function. You have 2 functions in your statement, (BSON2JSON and JSON_VAL), but without a full error message we can't guess which one is the problem. — Ian Bjorhovde, Commented Feb 12, 2017 at 15:00
Possible duplicate of DB2-JSON facing -440 sql error while using JSON_VAL for indexing — mustaccio, Commented Feb 12, 2017 at 15:20
@IanBjorhovde To retrieve the value of certain field in a JSON document, we need to invoke a JSON_VAL function. It's a built-in function that provides an SQL interface to extract and retrieve JSON data into SQL data types from BSON objects. Its schema is SYSIBM. This function only accepts the BSON type of JSON document. So its argument needs to be a column from the table that contains JSON document in BSON format or a SYSTOOLS.JSON2BON function that returns the BSON format of JSON document. so here when i'm using the above mentioned query i the error. can't able to predict this. — Dhinesh Pazanisamy, Commented Feb 13, 2017 at 5:40
What is the datatype of CUSTOMERDATA? could you show the DDL of JSONPO? — piet.t, Commented Feb 20, 2017 at 6:56
@piet.t CREATE TABLE JSONPO ( ID INTEGER NOT NULL, CUSTOMER DATA BLOB(16M) INLINE LENGTH 25000, PRIMARY KEY(ID)) CCSID UNICODE; — Dhinesh Pazanisamy, Commented Feb 21, 2017 at 3:56

Bimal Jha · Accepted Answer · 2021-05-20 11:25:01Z

0

In the year 2017, it should be called as SYSTOOLS.JSON_VAL if your database is enabled for JSON data. I see in your query, the schema name is missing. In latest version of db2 as of today, your query should work. For more info, you can refer db2 documentation here: https://www.ibm.com/docs/en/db2/11.5?topic=functions-json-val

answered May 20, 2021 at 11:25

Bimal Jha

4094 silver badges14 bronze badges

Add a comment |

Collectives™ on Stack Overflow

How to invoke json_val function in db2 json?

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related