How to convert JSON string to JSON in Oracle

Question

I need to convert JSON string to JSON in Oracle query.

Example String:

{\"1\":{\"qid\":1,\"aid\":1,\"a\":\"Yes\",\"isdyn\":0},\"2\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1},\"3\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1}}

and then store into an Oracle Column Table with a constraint of Valid JSON.

How can I do the same in Oracle and what is the best approach?

Oracle by itself has no meaning. Even Oracle 12c has no meaning. What is your full version number? The answer will likely depend on that. — user5683823
– user5683823, Commented Sep 7, 2018 at 20:22
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit — Tajinder
– Tajinder, Commented Sep 8, 2018 at 9:44

Kaushik Nayak · Accepted Answer · 2018-09-08 06:45:57Z

1

Use REPLACE to remove those backslashes before quotes. You could then insert them into a table and store it as a normal JSON.

CREATE TABLE t_json (
     id         INTEGER
          GENERATED ALWAYS AS IDENTITY,
     json_col   CLOB
          CONSTRAINT ensure_json CHECK ( json_col IS JSON ) --Is a valid json
);

INSERT INTO t_json ( json_col )
     SELECT replace('{\"1\":{\"qid\":1,\"aid\":1,\"a\":\"Yes\",\"isdyn\":0},\"2\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1},\"3\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1}}'
    ,'\"','"')
     FROM dual;

1 row inserted.

Access the elements

select t.json_col."1".a from t_json t;

A                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----
Yes

edited Sep 8, 2018 at 6:45

answered Sep 8, 2018 at 6:37

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

3 Comments

Tajinder Over a year ago

Using the same approach, however, what if there is a special character in actual JSON like\"a\":\"Yes\#\" then \# also needs to be replaced with #. I think when JSON object is converted to JSON string the \ is appended with every special character. So, the logic will be like replace(\ any special character,any special character). Please suggest.

Kaushik Nayak Over a year ago

@Tajinder : You question is more about how to transform the string rather than converting to JSON. This question answers your original question. You may try REGEXP_REPLACE( '\"a\":\"Yes\#\"' ,'\\(["#])','\1') to replace anything followed by backlash with just the character itself. If you have more things to be replaced/modified, try and ask a separate question with proper details.You may accept this answer if you think it helped you.

Tajinder Over a year ago

Sorry if the question was not clear. The string given in the question was just an example. Hoping it is clear now. I have also edited the question. You can check and suggest.

Stew Ashton · Accepted Answer · 2018-10-03 19:41:35Z

0

with input as ( select
'{\"1\":{\"qid\":1,\"aid\":1,\"a\":\"Yes\",\"isdyn\":0},\"2\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1},\"3\":{\"qid\":2,\"aid\":7,\"a\":\"sdfbsjkb\",\"isdyn\":1}}'
txt
from dual
)
select json_value(
  '["' || txt || '"]',
  '$[0]'
  error on error
) json_txt
from input;

{"1":{"qid":1,"aid":1,"a":"Yes","isdyn":0},"2":{"qid":2,"aid":7,"a":"sdfbsjkb","isdyn":1},"3":{"qid":2,"aid":7,"a":"sdfbsjkb","isdyn":1}}

JSON_VALUE just unescaped everything for you.

Best regards, Stew Ashton

answered Oct 3, 2018 at 19:41

Stew Ashton

1,5199 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to convert JSON string to JSON in Oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related