title: How to search within MySQL JSON object array?
body: <p>Consider the following JSON object,</p>
  <pre class="lang-json prettyprint-override"><code>[
    {
      &quot;id&quot;: 5964460916832,
      &quot;name&quot;: &quot;Size&quot;,
      &quot;value&quot;: &quot;Small&quot;,
      &quot;position&quot;: 1,
      &quot;product_id&quot;: 4588516409440
    },
    {
      &quot;id&quot;: 5964460916833,
      &quot;name&quot;: &quot;Size&quot;,
      &quot;value&quot;: &quot;Medium&quot;,
      &quot;position&quot;: 2,
      &quot;product_id&quot;: 4588516409440
    },
    {
      &quot;id&quot;: 5964460916834,
      &quot;name&quot;: &quot;Size&quot;,
      &quot;value&quot;: &quot;Large&quot;,
      &quot;position&quot;: 3,
      &quot;product_id&quot;: 4588516409440
    }
  ]
  </code></pre>
  <p>This is a value present in a table field called <code>custom_attrs</code> of JSON data type in a MySQL 8.0 table. I wanted to search the JSON data to match with multiple fields in the same object.</p>
  <p>For example,</p>
  <p>I wanted to see if there's a match for <code>name</code> &quot;Size&quot; and <code>value</code> &quot;Medium&quot; within the same object. It should not match the name in the first object and the value in the second object.</p>
  <p>While we can always use JSON table, I don't prefer that due to the complexities it brings during the JOINs.</p>
  <p><code>JSON_SEARCH</code> supports LIKE operator, but it cannot ensure if it's from the same object
  <code>JSON_CONTAINS</code> supports multiple fields but not LIKE as follows,</p>
  <pre><code>SET @doc = CAST('[{&quot;id&quot;:5964460916832,&quot;name&quot;:&quot;Size&quot;,&quot;value&quot;:&quot;Small&quot;,&quot;position&quot;:1,&quot;product_id&quot;:4588516409440},{&quot;id&quot;:5964460916833,&quot;name&quot;:&quot;Size&quot;,&quot;value&quot;:&quot;Medium&quot;,&quot;position&quot;:2,&quot;product_id&quot;:4588516409440},{&quot;id&quot;:5964460916834,&quot;name&quot;:&quot;Size&quot;,&quot;value&quot;:&quot;Large&quot;,&quot;position&quot;:3,&quot;product_id&quot;:4588516409440}]' AS JSON);
  SELECT JSON_CONTAINS(@doc, '{&quot;name&quot;:&quot;Size&quot;, &quot;value&quot;:&quot;Small&quot;}')
  </code></pre>
  <p>Is there any way to get the same JSON_CONTAINS like functionality with partial search like, <code>{&quot;name&quot;:&quot;Size&quot;, &quot;value&quot;:&quot;%sma%&quot;}</code></p>
  <p>Any help on this would be greatly helpful.</p>