• I want to change my value '4444' to '7727'
• knowing that I only have the old value ('4444') and the new value ('7727') but not the key

["4444","5555"]

after modification

["7727","5555"]

CodePudding user response：

You can use FOR ORDINALITY in a JSON_TABLE:

SELECT *
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.json,
         '$[*]'
         COLUMNS
           idx FOR ORDINALITY,
           value NUMBER PATH '$'
       );

Which, for the sample data:

CREATE TABLE table_name (id   NUMBER PRIMARY KEY, json JSON);

INSERT INTO table_name (id, json)
SELECT 1, '["4444","5555"]' FROM DUAL;

Outputs:

ID	JSON	IDX	VALUE
1	["4444","5555"]	1	4444
1	["4444","5555"]	2	5555

Note: if you want the index to start from 0 then subtract 1.

If you want to get the values in PL/SQL then you can use JSON_ARRAY_T:

DECLARE
  ja JSON_ARRAY_T := JSON_ARRAY_T.PARSE('["4444","5555"]');
BEGIN
  FOR i IN 0 .. ja.get_size - 1 LOOP
    DBMS_OUTPUT.PUT_LINE( ja.get_number(i) || ' = ' || i );
  END LOOP;
END;
/

Which outputs:

4444 = 0
5555 = 1

db<>fiddle here

CodePudding user response：

Here is a function I made to modify a value of a json array from a value not from a key (old value = '4444', new value = '7727')

nmr_cmpt_json := JSON_ARRAY_T.parse(nmrs_cmpts_tab);
i := 0;
    loop
        exit when i> nmr_cmpt_json.get_Size ;
        if nmr_cmpt_json.get_String(i) = '4444' then
            nmr_cmpt_json.put(i, '7727', TRUE);
            nmrs_cmpts_tab:= nmr_cmpt_json .to_string;
            dbms_output.put_line( 'nmr_cmpt_json = ' || nmr_cmpt_json.stringify);
            exit;    
        end if;
        i := i  1;
    end loop;