title: Postgres Return jsonb values as jsonb array?
body: <p>tl;dr -- is there some way to get values as a jsonb_array from a jsonb object in postgres?</p>
  
  <hr>
  
  <p>Trying to use recursive cte in postgres to flatten an arbitrarily deep tree structure like this:</p>
  
  <pre><code>{
    "type": "foo",
    "properties": {...},
    "children": [
      "type": "bar",
      "properties": {...},
      "children": [
        "type": "multivariate",
        "variants": {
          "arbitrary-name": {
            properties: {...},
            children: [...],
          },
          "some-other-name": {
            properties: {...},
            children: [...],
          },
          "another": {
            properties: {...},
            children: [...],
          }
        }
      ]
    ]
  }
  </code></pre>
  
  <p>Generally following <a href="http://tatiyants.com/how-to-navigate-json-trees-in-postgres-using-recursive-ctes/" rel="nofollow noreferrer">this post</a>, however I'm stuck at processing the <code>type: "multivariate"</code> node where what I <em>really</em> want is essentially <code>jsonb_agg(jsonb_object_values(json_object -&gt; 'variants'))</code></p>
  
  <h2>Update:</h2>
  
  <p>Sorry, I obviously should have included the query I tried:</p>
  
  <pre><code>WITH RECURSIVE tree_nodes (id, json_element) AS (
    -- non recursive term
    SELECT
      id, node
    FROM trees
  
    UNION
  
    -- recursive term
    SELECT
      id,
      CASE
      WHEN jsonb_typeof(json_element) = 'array'
        THEN jsonb_array_elements(json_element)
      WHEN jsonb_exists(json_element, 'children')
        THEN jsonb_array_elements(json_element -&gt; 'children')
      WHEN jsonb_exists(json_element, 'variants')
        THEN (select jsonb_agg(element.value) from jsonb_each(json_element -&gt; 'variants') as element)
      END AS json_element
    FROM
      tree_nodes
    WHERE
      jsonb_typeof(json_element) = 'array' OR jsonb_typeof(json_element) = 'object'
  )
  
  select * from tree_nodes;
  </code></pre>
  
  <p>The schema is just an <code>id</code> &amp; a jsonb <code>node</code> column</p>
  
  <p>This query gives an error:</p>
  
  <pre><code>ERROR:  set-returning functions are not allowed in CASE
  LINE 16:       THEN jsonb_array_elements(json_element -&gt; 'children')
                      ^
  HINT:  You might be able to move the set-returning function into a LATERAL FROM item.
  </code></pre>
  
  <p>I just want <code>Object.values(json_element -&gt; 'variants')</code> </p>
  
  <h2>Update 2:</h2>
  
  <p>After reading this all again, I realized this is a problem due to me using a recent version of PostgreSQL (10.3), which apparently no longer allows returning a set from a <code>CASE</code> statement, which was kind of the crux of getting this tree-flattening approach to work afaict. There's probably some way to achieve the same thing in recent versions of PostgreSQL but I have no idea how I'd go about doing it.</p>