Skip to content
Advertisement

Put a json inside another json on Oracle 19

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

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement