Add support `JSON_SET` and `JSON_CONTAINS_PATH` functions

[pc-beast]

As MySQL and PostgreSQL have native support to store and query json data, there is a need to support json related queries in h2.

Currently if I try to use them I get error with below error message:

Method threw 'javax.persistence.PersistenceException' exception.
cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "JSON_CONTAINS_PATH" not found; SQL statement:
SELECT JSON_CONTAINS_PATH(metadata, 'one', CONCAT('$.', ?)) > 0 FROM user_metadata_v2 WHERE user_id = ? [90022-210]

I tried finding the code in the repository and couldn't find implementation. So my guess is this not implemented yet.

[katzyn]

H2 supports only standard JSON functions and operators and you need to use them with H2. Unfortunately, JSON_VALUE, JSON_QUERY, JSON_TABLE, and JSON_EXISTS aren't yet supported due to complexity of SQL/JSON path language, but you can use JSON simplified accessor to read elements of JSON:

SELECT (J)."a" IS NOT NULL, (J)."b" IS NOT NULL, (J)."c" IS NOT NULL
FROM (VALUES JSON '{"a": 8, "b": [1, 2]}') T(J);
> TRUE | TRUE | FALSE

Various DBMS have many own functions and operators and H2 cannot support them all. You can write user-defined functions JSON_SET and JSON_CONTAINS_PATH with some simple and possibly incomplete but good enough for your application implementations.

[pc-beast]

I understand. Thanks for pointing me to user defined functions.

However, what are the next set of JSON related functions that are being planned to be supported in near future? I'll be happy to take some of them up, if I have bandwidth in near future. Seems like an interesting problem to solve.

[grandinj]

@pc-beast that would be awesome! To my knowledge, no-one is currently working on adding more JSON support. So feel free to tackle whatever takes your fancy, just please stick to the SQL Standard syntax