db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6825) Add basic JSON support to Derby.
Date Mon, 07 Sep 2015 18:25:45 GMT

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

Rick Hillegas commented on DERBY-6825:

Thanks for test-driving the simpleJSON tool and thanks for that great feedback, Bryan. It
would be easy to add a jsonToCLOB() function to the simpleJSON tool. Something like this:

    public static Clob arrayToClob( JSONArray array )
        if ( array == null ) { return null; }
        else { return new HarmonySerialClob( array.toJSONString() ); }

...and then invoke it like this:

connect 'jdbc:derby:memory:db;create=true';

call syscs_util.syscs_register_tool( 'simpleJson', true );

create function jsonToClob( jsonDocument JSONArray ) returns clob
language java parameter style java no sql
external name 'FromJSON.arrayToClob';

create table employee( id int primary key, fullName varchar( 100 ) );
insert into employee values ( 1, 'Fred Flintstone' ), ( 2, 'Barney Rubble' );

create table docs( stringDoc clob, jsonDoc JSONArray );

insert into docs values
  jsonToClob( toJSON( 'select * from employee' ) ),
  toJSON( 'select * from employee' )

select * from docs;

I hesitate to productize the prettyPrint() function. It would not perform efficiently on large
documents. That's because it goes to some trouble to sort JSONObjects by their key values.
That's appropriate for a test function which needs to return deterministic results across
different JVM versions. But I don't think it's necessary for a publicly exposed function in
the simpleJSON tool.

What are your thoughts?


> 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:
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>             Fix For:
>         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

View raw message