Home » SQL & PL/SQL » SQL & PL/SQL » How to update the nested array in json clob column data using plsql..?
How to update the nested array in json clob column data using plsql..? [message #685399] |
Thu, 23 December 2021 23:48 |
|
jeevetha
Messages: 1 Registered: December 2021
|
Junior Member |
|
|
UPDATE sb_layout_defnSBL
SET SB_LAYOUT_DEFN_DATA = JSON_MERGEPATCH(SB_LAYOUT_DEFN_DATA,
'{ "screenFilterDefn":{"operator":"Has" }}'
)
WHERE JSON_VALUE(SBL.SB_LAYOUT_DEFN_DATA, '$.screenFilterDefn.operator') = 'HAS_NO_CASE';
I ran this update query it is updating the json objects. But my requirement is
screenFilterDefn" : [ {
"id" : "ART-FILTER-DEF-0",
"heading" : "Period",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : true,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Period Id",
"datasetId" : "PERIOD",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PERIOD_ID",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "DESC",
"datasetId" : "PERIOD",
"dateHierarchy" : "NONE",
"fieldName" : "PERIOD_ID",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-1",
"heading" : "Company",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : true,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Company Name",
"datasetId" : "COMPANY",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "COMPANY_NAME",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "COMPANY",
"dateHierarchy" : "NONE",
"fieldName" : "COMPANY_NAME",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-4",
"heading" : "Product Type",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Product Type",
"datasetId" : "PLAN",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PRODUCT_TYPE",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN",
"dateHierarchy" : "NONE",
"fieldName" : "PRODUCT_TYPE",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-5",
"heading" : "Billing Status",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Billing Status",
"datasetId" : "PLAN_HIST",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "BILLING_STATUS",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN_HIST",
"dateHierarchy" : "NONE",
"fieldName" : "BILLING_STATUS",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-6",
"heading" : "Plan Type",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Plan Type",
"datasetId" : "PLAN_HIST",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PLAN_TYPE",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN_HIST",
"dateHierarchy" : "NONE",
"fieldName" : "PLAN_TYPE",
"aggFn" : "NONE"
} ]
}
In this json i need to change the operator value as "contain" for example : "operator" : "IN" to "operator" : "contain"
I need the query for this..Help me to solve this
|
|
|
|
Re: How to update the nested array in json clob column data using plsql..? [message #685402 is a reply to message #685401] |
Fri, 24 December 2021 08:10 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Json you posted isn't well formed:
SQL> SELECT * FROM TBL
2 /
JSON_DOC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
screenFilterDefn" : [ {
"id" : "ART-FILTER-DEF-0",
"heading" : "Period",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : true,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Period Id",
"datasetId" : "PERIOD",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PERIOD_ID",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "DESC",
"datasetId" : "PERIOD",
"dateHierarchy" : "NONE",
"fieldName" : "PERIOD_ID",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-1",
"heading" : "Company",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : true,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Company Name",
"datasetId" : "COMPANY",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "COMPANY_NAME",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "COMPANY",
"dateHierarchy" : "NONE",
"fieldName" : "COMPANY_NAME",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-4",
"heading" : "Product Type",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ "art-tab-0", "art-tab-1", "art-tab-2", "art-tab-3", "art-tab-4", "art-tab-6", "art-tab-7", "art-tab-8", "art-tab-9" ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Product Type",
"datasetId" : "PLAN",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PRODUCT_TYPE",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN",
"dateHierarchy" : "NONE",
"fieldName" : "PRODUCT_TYPE",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-5",
"heading" : "Billing Status",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Billing Status",
"datasetId" : "PLAN_HIST",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "BILLING_STATUS",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN_HIST",
"dateHierarchy" : "NONE",
"fieldName" : "BILLING_STATUS",
"aggFn" : "NONE"
} ]
}, {
"id" : "ART-FILTER-DEF-6",
"heading" : "Plan Type",
"filterType" : "MULTI_SELECT",
"value" : [ ],
"validationType" : "ANY_TEXT",
"collectionId" : "PLAN_EXEC_STATISTICS_AGGREGATED",
"operator" : "IN",
"required" : false,
"autoLink" : true,
"linkedTabs" : [ ],
"linkedWidgets" : [ "ART-TABLE-0", "ART-TABLE-2", "ART-TABLE-1" ],
"scope" : "GLOBAL",
"columns" : [ {
"label" : "Plan Type",
"datasetId" : "PLAN_HIST",
"formatSpec" : "NO_FORMAT",
"dateHierarchy" : "NONE",
"hidden" : false,
"fieldName" : "PLAN_TYPE",
"aggFn" : "NONE"
} ],
"sortOrder" : [ {
"order" : "ASC",
"datasetId" : "PLAN_HIST",
"dateHierarchy" : "NONE",
"fieldName" : "PLAN_TYPE",
"aggFn" : "NONE"
} ]
}
SQL> SELECT * FROM TBL WHERE JSON_DOC IS JSON
2 /
no rows selected
SQL>
And JSONMERGEPATCH can worked with well-formed JSON only.
SY.
|
|
|
Goto Forum:
Current Time: Sun Sep 29 01:46:45 CDT 2024
|