CODEWITHSUNDEEP
oracle-databaseplsql
redsoxlost
redsoxlost

Reputation: 1235

Remove escape characters from JSON_OBJECT output

So the problem is like below, I have a table which has a CLOB column and it stores JSON string. lets say the column name is message. Now I am generating another JSON containing this message field along with some extra fields. It looks like below

{
  "groupId": "12345",
  "payloads": [
    {
      "message": "JSON",
      "key_value": "200",
      "kafkaTopic": "XXXX",
      "encryptInd": "Y"
    },
    {
      "message": "JSON",
      "key_value": "100",
      "kafkaTopic": "XXXX",
      "encryptInd": "Y"
    }
  ]
}

I am using JSON_OBJECT and JSON_ARRAYAGG to generate the final JSON. the query looks like

SELECT     JSON_OBJECT(KEY 'groupId' VALUE 12345,
                       KEY 'payloads' VALUE JSON_ARRAYAGG(JSON_OBJECT(KEY 'message'   VALUE gko.message,
                       KEY 'key_value' VALUE gko.key_value,
                       KEY 'kafkaTopic'  VALUE gkor.topic_name,
                       KEY 'encryptInd' VALUE gkor.encrypt_ind RETURNING CLOB)
                       RETURNING CLOB) RETURNING CLOB )

but the problem is JSON message with the tag 'message' is coming with escape "\" characters. like below

{"groupId":12345,"payloads":[{"message":"{\"code\":\"SALE\",\"header\":{\"tranType\":\"

Please let me know how to avoid the escape characters in message.

Upvotes: 1

Views: 3092

Answers (1)

redsoxlost
redsoxlost

Reputation: 1235

After changing the query in the below way escape characters no longer comes

JSON_OBJECT(KEY 'message'   VALUE gko.message FORMAT JSON

Upvotes: 1

Related Questions