CLOB column and path access before/after

[Ja Steam]

Hello All,

Up until now I've used PL/JSON for very simple stuff and it works perfectly.
I'm working with a project planning tool which can for 1 add rows(tasks) above and below a current task.

So I'm thinking I will need to use a oracle table with a single CLOB column. I think I will need to use JSON path to properly position the inserts.

This is where I'm stuck on several points :
- how would I read the CLOB into json (select clob into tempclob) -> then

obj:= json (tempclob);

- how to position the insert. But I think I could help myself by altering my JSON stuckture?

My current structure is a standard oracle table with column, where I htp() it returns:

{"PROJECT_MGMT_ID":33,"PROJECT_MGMT_NAME":"New Sub Task","PROJECT_MGMT_START":"2012-03-04 00:00:00","PROJECT_MGMT_END":"2012-03-10 00:00:00","PROJECT_MGMT_BASE_START":"2012-03-04 00:00:00","PROJECT_MGMT_BASE_END":"2012-03-10 00:00:00","PROJECT_MGMT_HOURS":null,"PARENT_ID":32,"PROJECT_MGMT_ISLEAF":"TRUE","CUST_ID":null,"PROJECT_MGMT_PERCENT_DONE":0,"PROJECT_MGMT_ASSIGNEE":"2"},{"PROJECT_MGMT_ID":32,"PROJECT_MGMT_NAME":"New Task Below","PROJECT_MGMT_START":"2012-03-04 00:00:00","PROJECT_MGMT_END":"2012-03-10 00:00:00","PROJECT_MGMT_BASE_START":"2012-03-04 00:00:00","PROJECT_MGMT_BASE_END":"2012-03-10 00:00:00","PROJECT_MGMT_HOURS":null,"PARENT_ID":10,"PROJECT_MGMT_ISLEAF":"TRUE","CUST_ID":null,"PROJECT_MGMT_PERCENT_DONE":0,"PROJECT_MGMT_ASSIGNEE":"3"}

I'm not sure how to go from a table structure to a json structure while retaining the the aboe output.

- I can stay wtih the current structure

{"ID":1, "NAME":"Some task"},{"ID":2, "NAME":"another task"}

OR
-

{"1": {"ID":1, "NAME":"Some task"}, "11":,{"ID":11, "NAME":"Task Before #2"}, "2":,{"ID":2, "NAME":"another task"}}

Can anybody offer some insight?

Jan.

[Jonas Krogsboell]

I'm not sure if I understand your question. But it seems that you should be using json_list instead of json as your outer type.

[Ja Steam]

Hello Jonas,

Currently I have standrard oracle table called ori_project_mgmt(PROJECT_MGMT_ID, PROJECT_MGMT_NAMe …etc). When I htp the select * from ori_projects I get output as:

{"PROJECT_MGMT_ID":1, "PROJECT_MGMT_NAME":"Some task"},{"PROJECT_MGMT_ID":2, "PROJECT_MGMT_NAME":"another task"}

I would like to be able do things like add row (New task #3) before project_mgmt_id: 2

I'm assuming I would like to convert my my standard table into a JSON_LIST (as you suggested).

How would I tell PL/JSON, insert the new record before record project_mgmt_id: 2?

Its hard to explain, but in short the front EXTJS Store does things like insert row at rowindex +1 or rowindex -1.

And I would need to replicate this behavior inside oracle column (JSON CLOB) while still returning HTP in a simple format.

Thanks

Jan

[James Sumners]

You're not thinking this all the way through. You should have another table that maps "PROJECT_MGMT_ID" to "TASK_ORDER_NUMBER" (or similar). If you insist on keeping only one table, then you need another column for the task order number.  You would then use an ORDER BY clause to sort your query results into the correct order.

[Ja Steam]

Hi,

Actually come to think of it, that's not such a bad idea, this way I could simply invert the order of the task with +1 -1 (or what ever else). Perfect example of KISS.  Kicking myself now.

Thanks

Jan