Keep column name as values when using "parse json as struct"

Ask the Community
1

I want to convert strings like

"{"id":{"value":"12345","timestamp":"2024-04-29T15:46:00.140Z"},
"id2":{"value":"ABCDE","timestamp":"2024-04-29T15:46:00.140Z"},
"id3":{"value":"","timestamp":""}}"

into

"{"id":{"value":"12345","timestamp":"2024-04-29T15:46:00.140Z"},
"id2":{"value":"ABCDE","timestamp":"2024-04-29T15:46:00.140Z"}}"

getting rid of all instances where value and timestamp is empty.
To do this, I am parsing the json as struct and trying to remove all null values.
Is there a way to keep id and id2 as a value so I can construct the final output or is there a better way to do this?

2

Two follow up questions:

"{"id":{"value":"12345","timestamp":"2024-04-29T15:46:00.140Z"},
"id2":{"value":"ABCDE","timestamp":"2024-04-29T15:46:00.140Z"},
"id3":{"value":"","timestamp":""}}"

Is the value above the value for one row?

Is there any way you can process the data before it goes into a json format or is this the structure of the raw data?

Initial thoughts: Could you use regex to find the instances where the value and timestamp are empty and replace the entire row with an empty string?

1 Like
3

This is the raw format but regex would probably work! Thank you!