'Bag of words a json formatted value within a SQL Server tables column?

I have a json formatted value inside a column (named column2) in my table. It looks like this:

{
  "conversation_id": "projects/e44ftgAWERGaqrwgr3grg",
  "participant_id": "pCZXSAEWGFn/participants/",
  "reply_audio": {},
  "response": {
    "queryResult": {
      "action": "input.welcome",
      "allRequiredParamsPresent": true,
      "diagnosticInfo": {
        "webhook_latency_ms": 593
      },
      "fulfillmentMessages": [
        {
          "text": {
            "text": [
              "<speak><break time=\"50ms\"/> <prosody rate=\"90%\">For navigation <break time=\"150ms\"/> tell me where you’d like to go. <break time=\"200ms\"/> otherwise  <break time=\"50ms\"/> please say advisor</prosody> </speak>"
            ]
          }
        }
      ],
      "intent":........
    }
  }
}

I am trying to put all the words between the [{text}] and "]}}] element. So, ideally, I would want the output to look like this (each word is separated by a comma):

[For,navigation,tell,me,where,you’d,like,to,go,.,otherwise,please,say,advisor]

This is what I have so far:

SELECT
    sesh_id,date,
    CASE
        WHEN column1 IN ('index1')
            THEN ( REGEXP_REPLACE(REGEXP_REPLACE ( REGEXP_REPLACE(
      REGEXP_REPLACE(column2,"\\.",""),"\\?",""),"\\<SPEAK>","\\ . STALINE")
      ,"\\</SPEAK>","\\ ENDLINE ."))
            ELSE (column2)
    END ARRAY_of_words
FROM
    table1

Is there any fast way to accomplish this task? Any ideas or suggestions would be appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source