Can my DB?

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags)
VALUES ('Hello', ARRAY['sql', 'database']);

SELECT * FROM posts WHERE 'sql' = ANY(tags);

• Supports arrays of any built-in or user-defined type.
• Operators include @> (contains), <@ (contained by), && (overlap), ANY, and ALL.
• GIN indexes can be created on array columns for fast containment queries.
• Supports multidimensional arrays, array slicing, and array_agg() for aggregation.

-- No native array type. Use JSON arrays as a workaround:
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  tags JSON NOT NULL DEFAULT ('[]')
);

INSERT INTO posts (title, tags)
VALUES ('Hello', JSON_ARRAY('sql', 'database'));

SELECT * FROM posts
WHERE JSON_CONTAINS(tags, '"sql"');

• No native array column type.
• JSON columns with JSON_ARRAY(), JSON_CONTAINS(), and JSON_OVERLAPS() are the common workaround.
• Multi-valued indexes on JSON arrays are supported in MySQL 8.0+.

• MariaDB has no native array column type.
• Arrays can be simulated with JSON columns (JSON_ARRAYAGG, JSON_EXTRACT) or normalized to a separate related table.

CREATE OR REPLACE TYPE tag_list AS VARRAY(100) OF VARCHAR2(50);
/

CREATE TABLE posts (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR2(255) NOT NULL,
  tags tag_list DEFAULT tag_list()
);

INSERT INTO posts (title, tags)
VALUES ('Hello', tag_list('sql', 'database'));

• Supports VARRAY (fixed max size) and nested table collection types as native schema-level collection types.
• Collection column types require a named TYPE definition before table creation.
• No inline array literal syntax like PostgreSQL's ARRAY[...].
• Querying individual elements requires TABLE() unnesting or TREAT/CAST expressions.
• JSON arrays via JSON columns are also available in Oracle 21c+.

-- No native array type. Use JSON arrays as a workaround:
CREATE TABLE posts (
  id INT IDENTITY PRIMARY KEY,
  title NVARCHAR(255) NOT NULL,
  tags NVARCHAR(MAX) NOT NULL DEFAULT '[]'
);

INSERT INTO posts (title, tags)
VALUES ('Hello', '["sql","database"]');

SELECT * FROM posts
WHERE EXISTS (
  SELECT 1 FROM OPENJSON(posts.tags) WHERE value = 'sql'
);

• No native array column type.
• JSON stored in NVARCHAR(MAX) with OPENJSON() for querying is the common workaround.
• Table-valued parameters can pass arrays to stored procedures but are not a column type.
• STRING_SPLIT() can be used for simple delimited string arrays.

-- No native array type. Use JSON arrays as a workaround:
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT NOT NULL DEFAULT '[]'
);

INSERT INTO posts (title, tags)
VALUES ('Hello', json_array('sql', 'database'));

SELECT * FROM posts
WHERE EXISTS (
  SELECT 1 FROM json_each(posts.tags) WHERE value = 'sql'
);

• No native array column type.
• JSON1 extension (built-in since SQLite 3.38.0) provides json_array(), json_each(), and related functions.
• json_each() is a table-valued function useful for querying elements within a JSON array.

SELECT PARSE_JSON('[1,2,3]') AS arr;

SELECT (PARSE_JSON('[1,2,3]'))[0] AS first_element;

• The SUPER type can store JSON arrays and other semi-structured data.
• SUPER is not a typed array; it is a semi-structured type.
• No ARRAY_LENGTH, UNNEST, or typed array operations.
• JSON_PARSE and PartiQL syntax used for SUPER queries.

• DB2 SQL does not have a native array column type.
• Arrays exist in SQL PL (procedural code) as local variables but cannot be used as column types in tables.
• Use JSON/XML or a related table for array data.

SELECT ARRAY_CONSTRUCT(1, 2, 3) AS my_array;

SELECT f.value::INT
FROM t, LATERAL FLATTEN(input => t.arr_col) f;

• ARRAY type stores ordered semi-structured arrays.
• Supports ARRAY_CONSTRUCT, ARRAY_APPEND, ARRAY_PREPEND, ARRAY_SIZE, ARRAY_CONTAINS, ARRAY_SLICE.
• FLATTEN() for unnesting.
• Snowflake ARRAYs are semi-structured; elements can be any type, including objects or nested arrays.
• Stored in the VARIANT column family.

SELECT name, tag
FROM users, UNNEST(tags) AS tag;

• ARRAY<type> is a native typed array.
• Supports ARRAY_AGG, ARRAY_LENGTH, ARRAY_TO_STRING, ARRAY_CONCAT, UNNEST.
• Supports nested arrays in STRUCT.
• BigQuery arrays must be homogeneously typed (ARRAY<INT64>, ARRAY<STRING>, etc.).
• Cannot directly nest arrays; use ARRAY<STRUCT<...>> for nested structures.
• UNNEST() in FROM expands array elements to rows.