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 Go to next message
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
  • Attachment: Clob data.PNG
    (Size: 79.23KB, Downloaded 861 times)

Report message to a moderator

Re: How to update the nested array in json clob column data using plsql..? [message #685401 is a reply to message #685399] Fri, 24 December 2021 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

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 Go to previous message
Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Previous Topic: Split a row into multiple rows
Next Topic: Oracle porting a natural join
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 04:20:21 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.