Extracting Data from a JSON Blob Column

[data4thewin]

I have a table that contains a JSON Blob in one of it's columns.  Can I extract the data into a tabular format?

1. {"$version":1,"observations":{"pests":{"41":{"count":{"unit":{"id":3,"name":"per trap"},"value":12},"notes":"Found bug"}}}}
2. {"$version":1,"observations":{"general":{"50000":[{"count":{"unit":{"id":35},"value":30},"name":"Mildew"}]}}}
3. {"$version":1,"observations":{"pests":{"340":{"count":{"value":2,"unit":{"id":4,"name":"per leaf"}},"notes":"Live Mites"}}}}
4. {"$version":1,"observations":{"pests":{"36":{"count":{"value":13,"unit":{"id":45,"name":"per 180 sweep"}}}}}}
5. {"$version":1,"observations":{"general":{"50000":[{"name":"RV 11, 23N, 24N, 25, 26","notes":"Getting treated with raptor amd butyrac now. Larger weeds wont die but should slow down. "}]}}}

Thanks!

[v-shex-msft]

Hi @data4thewin,

Since each row has different element, I'd like to suggest you convert these to multiple tables with different structure.
After format these tables, you can use table.combine function to merge them.

Regards,

Xiaoxin Sheng

[data4thewin]

How would I get started converting them to multiple tables?

[v-shex-msft · Accepted solution]

Hi @data4thewin,

Perhaps you can refer to below sample file.

BTW, if you want format multiple records, I'd like recommend you use the records which has similar structure.

Regards,

XIaoxin Sheng

[data4thewin]

Thank you!!