如何拆分Json与甲骨文19

Question

有没有办法取出json的一部分？

我只想要没有zona和central的common对象。

我有下表

CREATE TABLE FB_TAB 
( COL CLOB COLLATE USING_NLS_COMP, 
ID NUMBER, 
TYPE VARCHAR2(20 BYTE) COLLATE USING_NLS_COMP, 
COLOR VARCHAR2(20 BYTE) COLLATE USING_NLS_COMP, 
AMOUNT NUMBER, 
APP VARCHAR2(20 BYTE) COLLATE USING_NLS_COMP, 
CONSTRAINT JSON_CON_1 CHECK (col IS JSON) ENABLE
)

及其插入

insert into fb_tab 
values('
{"common":{"contrato":{"id":"1","codigo":"054AKSDJ","nombre":"BUCLE"},"servicio":"","actividad":"Apertura","tipo_actividad":"BAJA","numero_administrativo":"","estado_origen":"Pendiente","provincia":{"id":"24","nombre":"León"},"aplicacion_origen":{"id":"1","nombre":"VISORD"},"zona":{"pais":"ES","cliente":"TL","div_geo":"2410002"},"central":{"codigo":"2410002","nombre":"Leon-Torre"},"clave":{"act_domiciliaria":"","prioridad":""}},"app_log":{"app_name":"client_mobile"}}
', 23, 'Ball', 'Red', 15, 'Mobile');
commit;

我想要得到下一个JSON结果

{"Type":"Ball","Color":"Red","App":"Mobile","Amount":"15","my_json":{"contrato":{"id":"1","codigo":"054AKSDJ","nombre":"BUCLE"},"servicio":"","actividad":"Apertura","tipo_actividad":"BAJA","numero_administrativo":"","estado_origen":"Pendiente","provincia":{"id":"24","nombre":"León"},"aplicacion_origen":{"id":"1","nombre":"VISORD"},"clave":{"act_domiciliaria":"","prioridad":""}}}

我正在尝试使用此查询

SELECT JSON_OBJECT (
'Type' value to_char(a.Type),
'Color' value to_char(a.Color),
'App' value to_char(a.App),
'Amount' value to_char(a.Amount),
'my_json' VALUE treat ( JSON_QUERY(a.col, '$.common' WITHOUT WRAPPER) as json )
)
--into json_output
FROM FB_TAB a
where a.id = :id;

但我的实际结果是这个

{"Type":"Ball","Color":"Red","App":"Mobile","Amount":"15","my_json":{"contrato":{"id":"1","codigo":"054AKSDJ","nombre":"BUCLE"},"servicio":"","actividad":"Apertura","tipo_actividad":"BAJA","numero_administrativo":"","estado_origen":"Pendiente","provincia":{"id":"24","nombre":"León"},"aplicacion_origen":{"id":"1","nombre":"VISORD"},"zona":{"pais":"ES","cliente":"TL","div_geo":"2410002"},"central":{"codigo":"2410002","nombre":"Leon-Torre"},"clave":{"act_domiciliaria":"","prioridad":""}}}

我不想看到zona和central

有办法做到这一点吗？

向致以最良好的问候

Answer 1

您试图插入表的JSON中有一个错误

这是修改后的插入声明

我已经把它分解成碎片，试图修复它，请放心"；打开"；它在单行中

insert into fb_tab 
values('
{"common":
{"contrato":{"id":"1","codigo":"054AKSDJ","nombre":"BUCLE"},
"servicio":"",
"actividad":"Apertura",
"tipo_actividad":"BAJA",
"numero_administrativo":"",
"estado_origen":"Pendiente",
"provincia":{"id":"24","nombre":"León"},
"aplicacion_origen":{"id":"1","nombre":"VISORD"},
"clave":{"act_domiciliaria":"","prioridad":""}
},
"zona":{"pais":"ES","cliente":"TL","div_geo":"2410002"},
"central":{"codigo":"2410002","nombre":"Leon-Torre"},
"app_log":{"app_name":"client_mobile"}
}',
23, 'Ball', 'Red', 15, 'Mobile');

基本上，您将clave放在central之后，并用两个大括号(}(将其闭合，使common包括zona和central，您试图将它们从查询结果中排除。

现在，当您查询表时

SELECT JSON_OBJECT (
'Type' value to_char(a.Type),
'Color' value to_char(a.Color),
'App' value to_char(a.App),
'Amount' value to_char(a.Amount)
,'my_json' VALUE treat ( JSON_QUERY(a.col, '$.common' WITHOUT WRAPPER) as json )
)
FROM FB_TAB a
where a.id = 23;

删除了:id绑定以简化调试过程

你得到想要的结果

{"Type":"Ball","Color":"Red","App":"Mobile","Amount":"15","my_json":{"contrato":{"id":"1","codigo":"054AKSDJ","nombre":"BUCLE"},"servicio":"","actividad":"Apertura","tipo_actividad":"BAJA","numero_administrativo":"","estado_origen":"Pendiente","provincia":{"id":"24","nombre":"León"},"aplicacion_origen":{"id":"1","nombre":"VISORD"},"clave":{"act_domiciliaria":"","prioridad":""}}}