title: Use Doctrine to search into a json database column
body: <p>I have a Symfony 3.2 project, and I need to <strong>filter data from a json column</strong>.
  Given that we have an entity named <em>"pack"</em> with a json column named <em>"settings"</em> containing this kind of data:</p>
  
  <pre><code>{
    "name": "My pack",
    "blocks": [
      {
        "name": "Block 1",
        "fields": [
          {"label": "A", "value": "57"},
          {"label": "B", "value": "100"}
        ]
      },
      {
        "name": "Bock 2",
        "fields": [
          {"label": "C", "value": "80"}
        ]
      }
    ]
  }
  </code></pre>
  
  <p>I have to search <em>packs</em> with a field which has the <em>label</em> "B" and its <em>value</em> at "100", but each <em>pack</em> doesn't have same blocks and fields order.
  So in my repository, using <strong>Doctrine\ORM\EntityRepository</strong> and <strong><a href="https://github.com/opsway/doctrine-dbal-postgresql" rel="nofollow noreferrer">opsway/doctrine-dbal-postgresql</a></strong> <em>(for GET_JSON_FIELD and GET_JSON_OBJECT functions)</em>, this kind of condition works:</p>
  
  <pre><code>use Doctrine\ORM\EntityRepository;
  
  class Packs extends EntityRepository
  {
    public function findFiltered(...)
    {
      return $this-&gt;createQueryBuilder('pack')
        -&gt;andWhere("GET_JSON_FIELD(GET_JSON_OBJECT(pack.settings, '{blocks,0,fields,1}'), 'label') = :label")
        -&gt;andWhere("GET_JSON_FIELD(GET_JSON_OBJECT(pack.settings, '{blocks,0,fields,1}'), 'value') = :value")
        -&gt;setParameter('label', 'B')
        -&gt;setParameter('value', '100')
      ;
    }
  }
  </code></pre>
  
  <p>But the problem is that I have to specify the precise block <em>(the first block object)</em>, and the precise field <em>(the second field object of the first block object)</em>. And my two condition aren't connected, it search if there is a label "B", then it search if there is a value "100". <strong>When I would like to have a research in all blocks and fields to find the good label for the good value.</strong> Any idea?</p>
  
  <hr>
  
  <p>I found the good SQL request for my problem:</p>
  
  <pre><code>SELECT *
  FROM pack p, json_array_elements(p.settings#&gt;'{blocks}') blocks, json_array_elements(blocks#&gt;'{fields}') fields
  WHERE  fields-&gt;&gt;'label' = 'B' and fields-&gt;&gt;'value' = '100';
  </code></pre>
  
  <p>But how I do that <strong>with doctrine</strong>?</p>