title: Searching jsonb array in PostgreSQL
body: <p>I'm trying to search a JSONB object in PostgreSQL 9.4. My question is similar to <a href="https://stackoverflow.com/questions/27681163/how-do-i-perform-a-case-insensitive-search-in-a-postgres-9-4-jsonb-column">this thread</a>.</p>
  
  <p>However my data structure is slightly different which is causing me problems. My data structure is like:</p>
  
  <pre><code>[
      {"id":1, "msg":"testing"}
      {"id":2, "msg":"tested"}
      {"id":3, "msg":"nothing"}
  ]
  </code></pre>
  
  <p>and I want to search for matching objects in that array by msg (RegEx, LIKE, =, etc). To be more specific, I want all rows in the table where the JSONB field has an object with a "msg" that matches my request.</p>
  
  <p>The following shows a structure similar to what I have:</p>
  
  <pre><code>SELECT * FROM 
      (SELECT 
          '[{"id":1,"msg":"testing"},{"id":2,"msg":"tested"},{"id":3,"msg":"nothing"}]'::jsonb as data) 
      as jsonbexample;
  </code></pre>
  
  <p>This shows an attempt to implement the answer to the above link, but does not work (returns 0 rows):</p>
  
  <pre><code>SELECT * FROM 
      (SELECT 
          '[{"id":1,"msg":"testing"},{"id":2,"msg":"tested"},{"id":3,"msg":"nothing"}]'::jsonb as data) 
      as jsonbexample 
  WHERE 
      (data #&gt;&gt; '{msg}') LIKE '%est%';
  </code></pre>
  
  <p>Can anyone explain how to search through a JSONB array? In the above example I would like to find any row in the table whose "data" JSONB field contains an object where "msg" matches something (for example, LIKE '%est%').</p>
  
  <hr>
  
  <h2>Update</h2>
  
  <p>This code creates a new type (needed for later):</p>
  
  <pre><code>CREATE TYPE AlertLine AS (id INTEGER, msg TEXT);
  </code></pre>
  
  <p>Then you can use this to rip apart the column with JSONB_POPULATE_RECORDSET:</p>
  
  <pre><code>SELECT * FROM 
      JSONB_POPULATE_RECORDSET(
          null::AlertLine, 
          (SELECT '[{"id":1,"msg":"testing"},
                    {"id":2,"msg":"tested"},
                    {"id":3,"msg":"nothing"}]'::jsonb 
           as data
          )
      ) as jsonbex;
  </code></pre>
  
  <p>Outputs:</p>
  
  <pre><code> id |   msg   
  ----+---------
    1 | testing
    2 | tested
    3 | nothing
  </code></pre>
  
  <p>And putting in the constraints:</p>
  
  <pre><code>SELECT * FROM 
      JSONB_POPULATE_RECORDSET(
          null::AlertLine, 
          (SELECT '[{"id":1,"msg":"testing"},
                    {"id":2,"msg":"tested"},
                    {"id":3,"msg":"nothing"}]'::jsonb 
           as data)
          ) as jsonbex 
  WHERE 
      msg LIKE '%est%';
  </code></pre>
  
  <p>Outputs: </p>
  
  <pre><code>id |   msg   
  ---+---------
   1 | testing
   2 | tested
  </code></pre>
  
  <p>So the part of the question still remaining is how to put this as a clause in another query.</p>
  
  <p>So, if the output of the above code = x, how would I ask:</p>
  
  <pre><code>SELECT * FROM mytable WHERE x &gt; (0 rows);
  </code></pre>