i have the next problem. I need create a json from a table that contain another json inside in one of the columns Having this:
CREATE TABLE BRUNOVS.TABLA_JSON_1 ( COL CLOB COLLATE USING_NLS_COMP, ID NUMBER, NAME VARCHAR2(50 BYTE) COLLATE USING_NLS_COMP, AGE NUMBER ) insert into tabla_json_1 (col, ID, NAME, AGE) values ( '{"totalResults":1,"limit":100,"offset":0,"items":[{"customerId":"24929","schedule":{"2021-03-24":{"freeTime":[["09:00","09:30"],["11:00","18:00"]],"arrivalTime":[{"min":"09:30","max":"10:30"},{"min":"11:30","max":"16:30"}]}}}]}', 4, 'Clara', 40 ); commit;
i try with this query:
SELECT JSON_OBJECT ( 'id' VALUE to_char(a.id), 'name' VALUE to_char(a.name), 'age' value to_char(a.age), 'original' value to_char(col) ) FROM tabla_json_1 a where a.id = 4;
But the result is not correct:
{"id":"4","name":"Clara","age":"40","original":"{"totalResults":1,"limit":100,"offset":0,"items":[{"customerId":"24929","schedule":{"2021-03-24":{"freeTime":[["09:00","09:30"],["11:00","18:00"]],"arrivalTime":[{"min":"09:30","max":"10:30"},{"min":"11:30","max":"16:30"}]}}}]}"}
Must be something like:
{"id":"4","name":"Clara","age":"40","original":{"totalResults":1,"limit":100,"offset":0,"items":[{"customerId":"24929","schedule":{"2021-03-24":{"freeTime":[["09: 00","09: 30"],["11: 00","18: 00"]],"arrivalTime":[{"min":"09: 30","max":"10: 30"},{"min":"11: 30","max":"16: 30"}]}}}]}}
There is a way to get the correct result?
Regards.
Advertisement
Answer
after see examples and see the resuslts. I know how do this:
SELECT JSON_OBJECT ( 'id' VALUE to_char(a.id), 'name' VALUE to_char(a.name), 'age' value to_char(a.age), 'original' value treat ( col as json ) -- this is the key ) FROM tabla_json_1 a where a.id = :agendamiento_id;
Thanks for all.
Best regards