Is there a Google Sheets or MS Excel formula to convert a column with JSON array to multiple rows

there are many ways one of which is:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(C2:C, ","))<>"", "♦"&A2:A&"♠"&B2:B&"♠"&
 REGEXEXTRACT(SPLIT(SUBSTITUTE(C2:C, """", ), ","), ":(\d+)"), ))
 ,,999^99)),,999^99)), "♦")), "♠"))

---

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A2), "♦"&A2&"♠"&B2&QUERY(TRANSPOSE("♠"&QUERY(REGEXEXTRACT(SPLIT(
 TRANSPOSE(SPLIT(SUBSTITUTE(C2, """", ), "{")), ","), ":(\d+)"), "offset 1", 0))
 ,,999^99), )),,999^99)),,999^99)), "♦")), "♠"))

One may adopt the code from my project.

json is a tricky format, it uses different data types and also may contain nested items:

{ key: { key1: "a", key2: "b" } }

This is the reason to use google-apps-script for the task.

Please, copy the sample file:

or see the source code here:

https://github.com/Max-Makhrov/GoogleSheets/blob/master/Json.gs

Sample usage:

=getJsonArrayAsTable(C2)