Regex for JSON parsing in Google Spreadsheet Cell

Question

I wanted to use the formula from this topic to extract "count" value in this JSON from a Google Sheet cell.

[{"year":2022,"month":2,"count":720},{"year":2022,"month":1,"count":720}, 
{"year":2021,"month":12,"count":480},{"year":2021,"month":11,"count":590}, 
{"year":2021,"month":10,"count":590},{"year":2021,"month":9,"count":590}, 
{"year":2021,"month":8,"count":320},{"year":2021,"month":7,"count":390}, 
{"year":2021,"month":6,"count":590},{"year":2021,"month":5,"count":590}, 
{"year":2021,"month":4,"count":720},{"year":2021,"month":3,"count":590}]

I've tried to build the correct regex but without success since I don't understand how the regex is build.

Answer 1

=TRANSPOSE(
  REGEXEXTRACT(
    A1,
    "\" & REGEXREPLACE(
            A1,
            CHAR(34)&"count" & CHAR(34) & ":(\d+)",
            CHAR(34)&"count" & CHAR(34) & ":($1)"
            )
  )
)

I took inspiration from here instead: https://stackoverflow.com/questions/43432409/multiple-regex-matches-in-google-sheets-formula. It's really clever and once you understand it, You'll end up wanting to use it all the time.

This will spit out a column (assuming you want it to be a column, if you don't, then unwrap it from the transpose function,) containing all values of count. A1 btw is the JSON string. I'm assuming that the JSON is all in a single cell.

I made a couple of sneaky changes here, first that "\" at line 4. I had to do since brackets are special characters in Regex. I also had to use CHAR(34) which is the equivalent of a double quotation mark. I couldn't find a way to escape it, so there's the quick, sloppy way of doing it. One last thing is that I placed the capture group only on the actual values. /"count":(\d+)/ is then replaced with "count":($1)". That then allows it to become a regex itself! (that technique I stole from the link I shared above.)

In the end, Regex is a monstrous thing to maintain, so don't beat yourself up if you can't figure it out.