Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 7 Sep 2015 18:25:45 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12843254.1436292503000.274071.1441650345821@Atlassian.JIRA>
In-Reply-To: <JIRA.12843254.1436292503000@Atlassian.JIRA>
References: <JIRA.12843254.1436292503000@Atlassian.JIRA>
 <JIRA.12843254.1436292503000@arcas>
Subject: [jira] [Commented] (DERBY-6825) Add basic JSON support to Derby.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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:

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

...and then invoke it like this:

{noformat}
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;
{noformat}

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?

Thanks,
-Rick


> 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)