'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 |
|---|
