CODEWITHSUNDEEP
jsonoracle-databaseoracle12c
Tajinder
Tajinder

Reputation: 2348

How to convert JSON string to JSON in Oracle

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?

Upvotes: 0

Views: 4776

Answers (2)

Stew Ashton
Stew Ashton

Reputation: 1529

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

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

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

Upvotes: 1

Related Questions