Manipulating JSON data with SQL in Oracle

Question

I have a CLOB field in my Oracle (v12.2) table which looks like this:

CREATE TABLE dmo_person (
    per_id RAW(16) CONSTRAINT NN_per_id NOT NULL,
    per_name VARCHAR2(128),
    per_tags CLOB CONSTRAINT dmo_pers_json_0 CHECK (per_tags IS JSON),
    CONSTRAINT sko_person_pk_0 PRIMARY KEY (per_id)
);

The JSON data has the following structure:

insert into dmo_person
 ( per_id, per_name, per_tags  )
values
 (
 sys_guid(),
 'John Doe',
 '{ "perm_admin" : 1, "perm_fileuser" : 0, "perm_subcon" : 1} ',
 );

So my question: Using an SQL update statement, how can I add another value like "perm_bigboss" : 1 to my CLOB? And is there an easy way to set a single value "perm_admin" : 0 using SQL?

see here stackoverflow.com/questions/41768597/…

OldProgrammer
– OldProgrammer

2018-02-03 19:08:10 +00:00
Commented Feb 3, 2018 at 19:08 — OldProgrammer
– OldProgrammer, Commented Feb 3, 2018 at 19:08

Kaushik Nayak · Accepted Answer · 2018-02-04 07:28:22Z

0

You could use the PUT method of JSON_OBJECT_T to add/set a single value.

Assuming that you update a single row( with a where clause), you may use this block. If there are multiple rows, use a loop or cursor.

 DECLARE
  v_pertags dmo_person.per_tags%TYPE;
  v_json_obj JSON_OBJECT_T;
  v_new_pertags dmo_person.per_tags%TYPE;
BEGIN
  SELECT per_tags
  INTO   v_pertags
  FROM   dmo_person; --where clause
  v_json_obj := TREAT(json_element_t.PARSE(v_pertags) AS json_object_t);
  v_json_obj.PUT('perm_bigboss' , 1);
  v_json_obj.PUT('perm_admin', 0);
  v_new_pertags := v_json_obj.to_string;

UPDATE dmo_person
  SET    per_tags = v_new_pertags; --where clause
END;
/

LIVESQL DEMO

edited Feb 4, 2018 at 7:28

answered Feb 4, 2018 at 7:18

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mockminister Over a year ago

Nice, thank you! No idea where I could have find this within Oracle documentation ..

mark d drake Over a year ago

JSON Documentation for 12.2 docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/… For PL/SQL in particular docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/…

Equipe sitsApp · Accepted Answer · 2019-12-06 08:52:12Z

For Oracle 11g version Json manupulation is not supported. So we must use basic functions : SUBSTR / INSTR / SUBSTR

create OR replace FUNCTION Get_Value(data_request VARCHAR2, s_key VARCHAR2) RETURN VARCHAR2
IS s_Value VARCHAR2(2000);

index1 integer := 0;
index2 integer := 0;

BEGIN 

s_Value := '';

SELECT INSTR(data_request, '"' || s_key || '":"' , 1, 1) INTO index1 FROM dual;

IF (index1 > 0) THEN
    index1 := index1 + LENGTH('"' || s_key || '":"') ;
    s_Value := SUBSTR(data_request, index1, LENGTH(data_request));
    SELECT INSTR(s_Value, '"', 1, 1) INTO index2 FROM dual;
    s_Value := SUBSTR(data_request, index1, index2-1);
END IF;

RETURN(s_Value); 

END;

Assume, we have a table 'tableName' having column 'data_request like : {"ip":"127.0.0.1","username":"userTest"}

We can then parse the json string like this :

SELECT data_request,  
Get_Value(data_request, 'numeroDeCompte'), Get_Value(data_request, 'ip') ip, 
Get_Value(data_request, 'username') username 
FROM tableName

Collectives™ on Stack Overflow

Manipulating JSON data with SQL in Oracle

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related