title: Amazon Redshift get all keys from JSON
body: <p>I looked at the documentation of Amazon redshift and I'm not able to see a function which will give me what I want.</p>
  
  <p><a href="https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html" rel="nofollow noreferrer">https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html</a></p>
  
  <p>I have a column in my database which contains JSON like this</p>
  
  <p>{'en_IN-foo':'bla bla', 'en_US-foo':'bla bla'}</p>
  
  <p>I want to extract all keys from json which have foo. So I want to extract</p>
  
  <p>en_IN-foo
  en_US-foo</p>
  
  <p>How can I get what I want? The closest to my requirement is JSON_EXTRACT_PATH_TEXT function but that can only extract the key when you know the key name. in my case I want all keys which have a pattern but I don't know the key names.</p>
  
  <p>I also tried abandoning the JSON function way and going the REGEX way. I wrote this code</p>
  
  <pre><code>select distinct regexp_substr('{en_in-foo:FOO, en_US-foo:BAR}','[^.]{5}-foo')
  </code></pre>
  
  <p>but this finds only the first match. I need all the matches.</p>