title: Query JSONB Array with Sequelize.js
body: <p>I'm working on a workflow engine of sorts where an admin can build a set of workflow steps, which have conditionals applied to them, and a user can then start a project and have the appropriate steps pulled up. </p>
  
  <p>The <code>conditional</code> object essentially has these key points:</p>
  
  <ul>
  <li>if <code>conditional.isDefault</code> is <code>true</code>, then it applies to all projects</li>
  <li>otherwise, <code>conditional</code> will have <code>fieldName</code>, <code>operand</code>, and <code>value</code> fields for numbers, and just <code>fieldName</code> and <code>value</code> fields for string matches (i.e. a <code>state</code> field defined by a select list of US States)</li>
  </ul>
  
  <p>I've got a straight SQL query (below) that works and illustrates what I'm doing, but I'm curious of 2 things:</p>
  
  <p>1) Is it possible to do this with sequelize instead of using raw sql?</p>
  
  <p>2) Is it more performant to have postgres do these ops, or would it be more performant to just get the entire set of workflow steps into my nodejs app and then iterate through the array in javascript?</p>
  
  <p>Here's the SQl query showing what I'm wanting to do with an array of data contained in a single JSONB field (NOTE: I'm not super skilled w/ SQL and especially new to postgres - so this may be all kinds of wrong!):</p>
  
  <pre><code>WITH steps(projId, step) AS (
      SELECT
          ppr.id AS projId,
          jsonb_array_elements(ppr."workflowSteps") AS step
      FROM "provider_project_ruleset" AS ppr
  )
  SELECT ARRAY(SELECT
      jsonb(steps.step)
  FROM 
      steps
  WHERE 
      steps.step -&gt; 'conditional' @&gt; '{"isDefault": true}'
      OR (step -&gt; 'conditional' -&gt;&gt; 'fieldName' = 'state' AND step -&gt; 'conditional' -&gt;&gt; 'value' = 'TN')
      OR (step -&gt; 'conditional' -&gt;&gt; 'fieldName' = 'amount' AND
          (CASE step -&gt; 'conditional' -&gt;&gt; 'operand'
              WHEN '&gt;=' THEN CASE WHEN  (step -&gt; 'conditional' -&gt;&gt; 'value')::bigint &lt;= 10000000000 THEN 0 ELSE 1 END
              WHEN '&lt;=' THEN CASE WHEN (step -&gt; 'conditional' -&gt;&gt; 'value')::bigint &gt;= 10000000000 THEN 0 ELSE 1 END
              WHEN '=' THEN CASE WHEN (step -&gt; 'conditional' -&gt;&gt; 'value')::bigint = 10000000000 THEN 0 ELSE 1 END
              ELSE 1
          END) = 0
      )
  )
  </code></pre>