JSON Formatting using Snowflake SQL to structure per Date

Question

My employer is currently a LiveChat client and we want to use their Data with their Api provided.

However, the APi is a JSON that is structured in a weird way and I find myself unable to transform it into a structured table per day.

this is a snippet of the JSON

  {
  "name": "duration-report",
  "records": {
    "2022-01-01": {},
    "2022-01-02": {},
    "2022-01-03": {
      "agents_chatting_duration": 229,
      "count": 6,
      "duration": 229
    },
    "2022-01-04": {
      "agents_chatting_duration": 1449,
      "count": 13,
      "duration": 1488
    },
    "2022-01-05": {
      "agents_chatting_duration": 1088,
      "count": 21,
      "duration": 1088
    }

My current code:

SELECT
"i"."DATA VALUE",
("i"."DATA VALUE":"name")::VARCHAR AS "name",
("i"."DATA VALUE":"records")::VARIANT AS "records",
("f1".Value)::VARIANT AS "date_content",
("f1".Value:"count")::NUMBER AS "count",
("f1".Value:"duration")::NUMBER AS "duration",
("f1".Value:"agents_chatting_duration")::NUMBER AS "agents_chatting_duration"
 FROM
 ( "$T{LIVECHAT_CHAT_DURATION}" ) "i",
 lateral flatten (input => "i"."DATA VALUE":"records") "f1"

I am basically missing a column containing the DATES. Can someone help me?

Thank you

Answer 1

You need OBJECT_KEYS function, since your SQL looks like not the final version and needs some cleaning I am adding Date as the first column

SELECT
OBJECT_KEYS("i"."DATA VALUE":"records")[row_number() over(order by data_value)-1] AS "dt",
"i"."DATA VALUE",
("i"."DATA VALUE":"name")::VARCHAR AS "name",
("i"."DATA VALUE":"records")::VARIANT AS "records",
("f1".Value)::VARIANT AS "date_content",
("f1".Value:"count")::NUMBER AS "count",
("f1".Value:"duration")::NUMBER AS "duration",
("f1".Value:"agents_chatting_duration")::NUMBER AS "agents_chatting_duration"
 FROM
 ( "$T{LIVECHAT_CHAT_DURATION}" ) "i",
 lateral flatten (input => "i"."DATA VALUE":"records") "f1"

OBJECT_KEYS returns an array, row_number is used to get the date for each row.

This is a workaround based on the sample data set you provided, might not work if your full data set don't follow the sample's pattern.

Note : Your JSON snippet is missing closing 2 curly braces