#15350: Writing JSON and JSONB to Postgres from AquaScript (with AQTableWriter or in general)

bobfromtn reported 2017-06-23T21:15:08Z · last modified 2017-07-06T17:52:07Z

Writing JSON and JSONB to Postgres from AquaScript (with AQTableWriter or in general)

Dev	tomconrad
QA	SachinPrakash

Priority	Major
Complexity	Unknown

Component	DB - PostgreSQL
Version	18.0

I was trying to write a JSON string and/or object to a PostgreSQL JSON or JSONB column. I saw the exception that AQTableWriter does not support modifying JSON or JSONB columns currently. I was able to write my JSON into a TEXT column.

1) Is there an alternate API currently available that would allow updating / inserting into an actual JSON or JSONB column in a Postgres table?

2) Is there a way to invoke a Postgres stored procedure, which might in turn write natively into JSON/JSONB? In fact, I have not noticed any examples of calling a stored procedure from AquaScript.

3) Is there a time line currently anticipated when Postgres support for JSON/JSONB will be available in AQTableWriter, or perhaps more generally in AquaScript?

All Comments (5) Change History

SachinPrakash 2017-06-24T14:45:24Z · (edited)

@tom: Look at DSTableWriter.java. Line 196 checks to see if colInfo._isEditable. For JSON, it returns false. However, JSON is editable via our table data editor. Also, Line 483 has code to supprt JSON data type. Examine what happens if you set colInfo._isEditable to be true.

Perform a similar exercise for JSONB as well.

PG Doc: JSON & JSONB

Once finished w/ your analysis, lets discuss

tomconrad 2017-06-26T21:26:06Z

V18 svn #54886 and V19 svn #54885 - Added JSON and JSONB to PostgreSQL Aquascript data inserts.

Here is an example testcase:

1) Create the table using QA.

CREATE TABLE "public"."t2" (

json_test json NOT NULL,

jsonb_test jsonb NOT NULL

)

2) This Aquascript inserts json data into the table created above.

//*******************************************************************

// Input parameters.

//*******************************************************************

var server = "PostgreSQL 10.1.1.69 v9.4"

var database = "chinook";

var schema = "public";

var conn = aqua.project.getServerConnection(server);

conn.connect();

conn.changeDatabase(database);

var metadata = conn.getMetadata();

var table = metadata.extractTable(database, schema, "t2");

var columns = table.extractColumns();

var dataWriter = table.getWriter();

var dataRow = dataWriter.newDataRow();

// Populate non-nullable fields

//json field

var jsonString1 = '{ "pageInfo" : { "pageName" : "abc" ,"pagePic" : "http://example.com/content.jpg" } ,'

+ '"posts" : [{"post_id" : 12345, "actor_id" : "544","page_id" : 977}]}';

var jsonString2 = '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }';

dataRow.set(0, jsonString1);

dataRow.set(1, jsonString2);

dataWriter.write(dataRow);

aqua.console.print("inserted row");

dataWriter.close();

conn.disconnect();

tomconrad 2017-06-26T21:36:09Z

Hi QA Team,

Please regression test that the json and jsonb work across Table editor, ER modeler, Import, Export and Schema script generator for PostgreSQL. Create some test cases similar to the test case above and confirm that inserts into a JSON and JSONB column from Aquascript are now working and accurate.

Thanks, Tom

tariqrahiman 2017-06-30T01:53:28Z

Verified in ADS v18.0.17-2

SachinPrakash 2017-06-30T16:51:09Z

The below patch addresses this issue:

Patch : http://www.aquafold.com/download/v18.0.0/ads-18.0.17-2-patch.zip

Update Instructions : http://www.aquafold.com/support-update.html#v18

Search Tips

Aqua Data Studio / nhilam

Writing JSON and JSONB to Postgres from AquaScript (with AQTableWriter or in general)

Issue #15350

Completion

2 issue links

Issue #15356

Issue #15355

Search Tips

Aqua Data Studio / nhilam

Title

Writing JSON and JSONB to Postgres from AquaScript (with AQTableWriter or in general)

Issue #15350

Completion

2 issue links

Issue #15356

Issue #15355