Remove escape characters from JSON_OBJECT output

Question

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.

redsoxlost · Accepted Answer · 2020-12-14 15:12:44Z

2

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

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

answered Dec 14, 2020 at 15:12

redsoxlost

1,2557 gold badges22 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Remove escape characters from JSON_OBJECT output

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related