title: Getting Values From Json Data Inside Array in Mysql
body: <p>We are saving information in a json Column which contain json data in an array.</p>
  
  <p>Data structure:</p>
  
  <pre><code>[
      {
          "type":"automated_backfill",
          "title":"Walgreens Sales Ad",
          "keyword":"Walgreens Sales Ad",
          "score":4
      },
      {
          "type":"automated_backfill",
          "title":"Nicoderm Coupons",
          "keyword":"Nicoderm Coupons",
          "score":4
      },
      {
          "type":"automated_backfill",
          "title":"Iphone Sales",
          "keyword":"Iphone Sales",
          "score":3
      },
      {
          "type":"automated_backfill",
          "title":"Best Top Load Washers",
          "keyword":"Best Top Load Washers",
          "score":1
      },
      {
          "type":"automated_backfill",
          "title":"Top 10 Best Cell Phones",
          "keyword":"Top 10 Best Cell Phones",
          "score":1
      },
      {
          "type":"automated_backfill",
          "title":"Tv Deals",
          "keyword":"Tv Deals",
          "score":0
      }
  ]
  </code></pre>
  
  <p>What we are trying:</p>
  
  <pre><code>SELECT id, ad_meta-&gt;'$**.type' FROM window_requests
  </code></pre>
  
  <p>that returns:</p>
  
  <p><a href="https://i.stack.imgur.com/fBGcE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fBGcE.png" alt="enter image description here"></a></p>
  
  <p>We are looking to get each type as row, which i think only possible with stored procedure, return whole column and then run loop on each row and return data... </p>
  
  <p>Or can you think of a better solution?</p>
  
  <p>Either Update Architecture:</p>
  
  <p>or should we change our database and save information in separate table instead to json column ?</p>
  
  <p>And then we can get easily join to get data with adding a foreign key.</p>
  
  <p>Thanks you.</p>