Can my DB?

-- Expand an array of JSON objects into rows
SELECT t.id, t.name
FROM departments d,
  json_to_recordset(d.employees) AS t(id INT, name TEXT);

-- Expand a simple JSON array
SELECT elem
FROM json_array_elements('[1,2,3]'::json) AS elem;

-- Key-value iteration
SELECT key, value
FROM json_each('{"a":1,"b":2}'::json);

• No JSON_TABLE function. Uses json_to_recordset(json_col) AS t(col1 type, col2 type) for array-of-objects expansion.
• json_each() / json_each_text() iterate over top-level key-value pairs.
• json_array_elements() / jsonb_array_elements() unnest a JSON array into rows.
• Output columns must be declared explicitly in the AS clause for json_to_recordset.

SELECT jt.id, jt.name
FROM departments d,
  JSON_TABLE(
    d.employees,
    '$[*]' COLUMNS (
      id   INT          PATH '$.id',
      name VARCHAR(100) PATH '$.name'
    )
  ) AS jt;

• Full SQL:2016 JSON_TABLE with a COLUMNS clause.
• Supports NESTED PATH for expanding nested arrays into additional rows.
• ON EMPTY and ON ERROR clauses control handling of missing or malformed values.

SELECT jt.id, jt.name
FROM departments d,
  JSON_TABLE(
    d.employees,
    '$[*]' COLUMNS (
      id   INT          PATH '$.id',
      name VARCHAR(100) PATH '$.name'
    )
  ) AS jt;

• JSON_TABLE added in MariaDB 10.6 with the same SQL:2016 syntax as MySQL.
• Supports NESTED PATH for expanding nested arrays.

SELECT jt.id, jt.name
FROM departments d,
  JSON_TABLE(
    d.employees,
    '$[*]' COLUMNS (
      id   NUMBER       PATH '$.id',
      name VARCHAR2(100) PATH '$.name'
    )
  ) jt;

• JSON_TABLE available since Oracle 12.2.
• Supports NESTED PATH for expanding nested JSON arrays into additional rows.
• ERROR ON ERROR / NULL ON ERROR clauses control error handling.

-- OPENJSON with explicit schema (WITH clause)
SELECT jt.id, jt.name
FROM departments d
CROSS APPLY OPENJSON(d.employees, '$')
  WITH (
    id   INT           '$.id',
    name NVARCHAR(100) '$.name'
  ) AS jt;

-- OPENJSON without schema returns key/value/type rows
SELECT [key], value, type
FROM OPENJSON('{"a":1,"b":"hello"}');

• OPENJSON is the SQL Server equivalent of JSON_TABLE, using a WITH clause instead of COLUMNS.
• Without WITH, OPENJSON returns a generic (key, value, type) rowset.
• Requires compatibility level 130 (SQL Server 2016+).

-- Expand a JSON array
SELECT value
FROM json_each('[1,2,3]');

-- Expand an array of objects stored in a column
SELECT
  json_extract(e.value, '$.id')   AS id,
  json_extract(e.value, '$.name') AS name
FROM departments d,
  json_each(d.employees) e;

• json_each(json, path) is a table-valued function that expands a JSON array into rows.
• json_tree(json, path) performs recursive traversal of any JSON value.
• No JSON_TABLE; column values must be extracted manually with json_extract().

-- Unnest a JSON array stored in SUPER
SELECT
  elem.id,
  elem.name
FROM departments d,
     d.employees AS elem;

-- Equivalent UNNEST form
SELECT elem
FROM departments d,
     UNNEST(d.employees) AS elem;

• Redshift uses PartiQL iteration and UNNEST over SUPER values rather than a JSON_TABLE function name.
• SUPER arrays can be expanded to rows directly in the FROM clause.
• Object unpivoting is also supported for JSON object shredding.

-- Db2 JSON_TABLE (newer versions)
SELECT jt.id, jt.name
FROM departments d,
  JSON_TABLE(
    d.employees,
    '$[*]' COLUMNS (
      id   INTEGER      PATH '$.id',
      name VARCHAR(100) PATH '$.name'
    )
  ) AS jt;

• Db2 documents a native JSON_TABLE function.
• XMLTABLE-based approaches were used in older versions before native JSON_TABLE support.
• Supports the SQL/JSON row-and-column shredding pattern directly.

SELECT
  f.value:id::INTEGER  AS id,
  f.value:name::STRING AS name
FROM departments d,
  LATERAL FLATTEN(input => PARSE_JSON(d.employees)) f;

• FLATTEN(input, path, outer, recursive, mode) is the Snowflake equivalent of JSON_TABLE.
• Returns columns: SEQ, KEY, PATH, INDEX, VALUE, THIS.
• Element fields are accessed with the colon operator: f.value:field_name.
• LATERAL FLATTEN is the standard usage pattern within a FROM clause.

-- Expand a JSON array column
SELECT
  JSON_VALUE(elem, '$.id')   AS id,
  JSON_VALUE(elem, '$.name') AS name
FROM departments d,
  UNNEST(JSON_QUERY_ARRAY(d.employees, '$')) AS elem;

-- Native ARRAY of STRUCT avoids JSON entirely
SELECT emp.id, emp.name
FROM departments d,
  UNNEST(d.employees_struct) AS emp;

• JSON_QUERY_ARRAY(json_col, path) extracts a JSON array; UNNEST() expands it into rows.
• JSON_VALUE() and JSON_QUERY() extract scalars and sub-objects respectively.
• Native STRUCT and ARRAY types are the idiomatic BigQuery alternative to JSON shredding.
• No JSON_TABLE function name, but the shredding capability is present through JSON_QUERY_ARRAY plus UNNEST.