[jira] [Commented] (DERBY-6825) Add basic JSON support to Derby.

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6825?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14732056#comment-14732056 ] 

Bryan Pendleton commented on DERBY-6825:
----------------------------------------

Hi Rick, I was playing with this feature today to see how it works.

I found that trying to use the toJSON function was a bit frustrating, because
the JSONArray type doesn't want to be converted to other simpler types
very easily.

For example:

    insert into b2 (a) values( toJSON( 'select * from bry1' ) );
    ERROR 42821: Columns of type 'VARCHAR' cannot hold values of type '"APP"."JSONARRAY"'.

and

    ij> update b2 set a = cast( toJSON( 'select * from bry1' ) as varchar(1000));
    ERROR 42846: Cannot convert types '"APP"."JSONARRAY"' to 'VARCHAR'.

I eventually succeeded, by defining the prettyPrint function from the test suite,
as you described above, but that wasn't a very good solution.

Is it possible to make it easier to convert between the JSONArray UDT and
types such as VARCHAR?

Secondly, is it possible that we could refactor the prettyPrint function out
of the test suite and into the simpleJSON tool itself? It seems like a useful
function; it doesn't seem like very much code, and as long it would live
in the optionaljar, it wouldn't increase the base product footprint...

> Add basic JSON support to Derby.
> --------------------------------
>
>                 Key: DERBY-6825
>                 URL: https://issues.apache.org/jira/browse/DERBY-6825
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.12.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>             Fix For: 10.12.0.0
>
>         Attachments: SimpleJsonVTI.java, derby-6825-01-aa-optionalTool.diff, derby-6825-01-ab-optionalTool.diff, derby-6825-02-aa-vti.diff, derby-6825-03-aa-datatypeTests.diff, derby-6825-04-aa-grantPrivileges.diff
>
>
> Other database are adding support for the popular JSON data interchange format defined by https://www.ietf.org/rfc/rfc4627.txt. We could add a VTI to Derby which would unpack a JSON document into a set of rows. We could also add a reverse transformation, which turns a query result into a JSON document.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)