CODEWITHSUNDEEP
jsonregexgoogle-sheetsarray-formulasre2
O1R
O1R

Reputation: 27

json parse. Extract 2 values from multiple records and merge into one cell, with array formula

I'm trying to use a formula to extract the values from each "hash" in table cells A1:A and concatenate them in cells B1:B separated by commas

https://docs.google.com/spreadsheets/d/1i2qLX-BfZ6ZpWNzpgAVHsNSTG8HjwbhSFVfqtp8bMhc/edit?usp=sharing

JSON

A1

{
"2Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo":{
"hash":"2Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo",
"name":"logo2.png",
"description":"",
"type":"image/png",
"path":"/logo2.png",
"size":"13 KB"
},
"1W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD-s":{
"hash":"1W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD-s",
"name":"logo.png",
"description":"",
"type":"image/png",
"path":"/logo.png",
"size":"29 KB"
}
}

A2

{
"5Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo":{
"hash":"5Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo",
"name":"logo2.png",
"description":"",
"type":"image/png",
"path":"/logo3.png",
"size":"13 KB"
},
"4W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD-s":{
"hash":"4W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD-s",
"name":"logo.png",
"description":"",
"type":"image/png",
"path":"/logo4.png",
"size":"29 KB"
}
}

Extracted value (Hash)

B1

2Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo,1W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD

B2

5Qk1xsOqhlxUe5d3fiJVcXgBLaBODegmo,4W2HdCGmeMLz1u0kzvxdUhGyEwIUlMD

Upvotes: 0

Views: 209

Answers (2)

player0
player0

Reputation: 1

Try:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IFNA(REGEXREPLACE(SPLIT(REGEXEXTRACT(
 REGEXREPLACE(A2:A, "[""\{\}\n ]|-s", ), "hash:.*"), "hash:", ), 
 "name.*", ))),,9^9))), " |,$", ))

enter image description here

Upvotes: 2

doubleunary
doubleunary

Reputation: 18819

Use regexextract(), like this:

=arrayformula( 
  textjoin( 
    ",", 
    true, 
    iferror( 
      regexextract( 
        query( 
          transpose( split(A2, "," & char(10), true, true) ), 
          "where Col1 contains 'hash' ", 0 
        ), 
        "hash"":""(\w+)"
      ) 
    ) 
  ) 
)

Upvotes: 0

Related Questions