Brandon

Nathaniel T. we're excited to try the Giga instance type and are on a business plan, but it's not appearing in the dropdown options for either service accounts or the Settings > Ducklings page. We've hard-refreshed, logged out and back in, but still nothing. When is that expected to actually be available to users?

thousands of files of SQL

Hoo! Sounds like you have a different kind of problem there, that's a wild number of queries to maintain containing JSON logic. Good luck!

Taking it a step further to provide a clear input table context and extremely varied input JSON structures:

PIVOT (
  WITH RECURSIVE json_recursive AS (
    SELECT 
      tbl.row_num,
      key, 
      value, 
      json_type(value) as type
    FROM (
      SELECT 1 as row_num, '[{"a": 1, "b": {"c": 2, "d": 3}}, {"x": "test"}]' as val 
      UNION ALL
      SELECT 2 as row_num, '{"r": 10000}' as val
    ) as tbl,
    json_each(tbl.val)
    
    UNION ALL
    
    SELECT 
      parent.row_num,
      child.key, 
      child.value, 
      json_type(child.value)
    FROM json_recursive parent, json_each(parent.value) child
    WHERE parent.type IN ('OBJECT', 'ARRAY')
  )
  SELECT row_num, key, value FROM json_recursive
  WHERE type NOT IN ('OBJECT', 'ARRAY')
) ON key USING any_value(value);

It really doesn't get much more elegant than that for JSON parsing, to be frank

You can even do it recursively with a CTE pretty easily, returns the other attached table and handles any depth of JSON nesting:

PIVOT (
  WITH RECURSIVE json_recursive AS (
    SELECT key, value, json_type(value) as type
    FROM json_each('{"a": 1, "b": {"c": 2, "d": 3}}')
    UNION ALL
    SELECT child.key, child.value, json_type(child.value)
    FROM json_recursive parent, json_each(parent.value) child
    WHERE parent.type IN ('OBJECT', 'ARRAY')
  )
  SELECT key, value FROM json_recursive
  WHERE type NOT IN ('OBJECT', 'ARRAY')
) ON key USING any_value(value);

Tim M. not that it really matters to me, but I was surprised you couldn't do what you wanted with json_each and a dynamic pivot. I just quickly pulled your sample and you absolutely can if you know the depth:

PIVOT (
  SELECT n.key, n.value
  FROM json_each('[{"a":1},{"b":2}]') as x,
       json_each(x.value) as n
) ON key USING any_value(value);

Returns the attached table.

Have you tried json_each yet? That doesn't require any schema. JSON is a full data type with different, looser expectations than struct. If you don't need struct for some other reason, I wouldn't bother trying to convert to it. We also opted out of doing that and just accepted the bit of extra overhead for JSON Your sample unnests just fine with json_each:

install json;
load json;
select r.value from json_each('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as r;

Chiming in with my 2 cents from our company's needs re: JSON. We ended up finding equivalents of the majority of PG functions, with the most common shift being towards usage of json_each which looks a lot like what you might be trying to do. Of note, that spec returns an object which contains a value field which, if you just expected a value, should be pulled. I don't know about the recursive part, we usually have a known depth for our JSON objects with potentially varying fields. Since it wasn't mentioned yet, the JSON table functions were very helpful for our migration. I pretty heavily utilized an LLM to evaluate all of the different code we had on Postgres, and luckily almost everything cleanly lifts and shifts to DuckDB dialect. Array functions, JSON functions, and some data type things were the trickiest, but honestly most queries worked out of the box or with very minor adjustments.

For a second I thought Lars might be part of the MotherDuck team announcing the feature, but it seems like perhaps this is a feature request? I'd be interested in something like this as well, FWIW.