Can my DB?

SELECT
  payload ->> 'type' AS event_type,
  payload -> 'x' AS x_value,
  jsonb_path_query_first(payload, '$.x') AS x_via_path
FROM events
WHERE payload @> '{"type": "click"}';

• Operators -> and ->> extract JSON object fields; -> returns JSON, ->> returns text.
• SQL/JSON path functions (jsonb_path_query, jsonb_path_exists) added in PostgreSQL 12.
• GIN indexes on JSONB columns accelerate containment (@>) and existence (?) queries.

SELECT
  JSON_EXTRACT(payload, '$.type') AS event_type,
  payload ->> '$.type' AS event_type_unquoted,
  JSON_VALUE(payload, '$.x') AS x_value
FROM events
WHERE JSON_EXTRACT(payload, '$.type') = '"click"';

• JSON_EXTRACT() returns a JSON value; ->> is shorthand that also unquotes strings.
• JSON_VALUE() was added in MySQL 8.0.21 and returns a scalar as a SQL type.
• Path expressions use $ as the root with dot notation and bracket syntax for arrays.

SELECT
  JSON_EXTRACT(payload, '$.type') AS event_type,
  payload->>'$.type' AS event_type_unquoted
FROM events
WHERE JSON_EXTRACT(payload, '$.type') = '"click"';

• JSON_EXTRACT(col, '$.path'), shorthand -> operator (col->'$.path'), ->> for unquoted value.
• JSON_SEARCH(), JSON_CONTAINS() also available.
• MariaDB JSON functions are compatible with MySQL.
• -> and ->> operators are convenient shorthand.

SELECT
  JSON_VALUE(payload, '$.type') AS event_type,
  JSON_QUERY(payload, '$.x') AS x_value
FROM events
WHERE JSON_EXISTS(payload, '$.type?(@ == "click")');

• JSON_VALUE() extracts a scalar value; JSON_QUERY() extracts an object or array.
• JSON_EXISTS() tests whether a path expression matches any values.
• Supports the SQL/JSON path language with filter expressions.

SELECT
  JSON_VALUE(payload, '$.type') AS event_type,
  JSON_QUERY(payload, '$.nested') AS nested_obj
FROM events
WHERE JSON_VALUE(payload, '$.type') = 'click';

• JSON_VALUE() extracts a scalar value; JSON_QUERY() extracts an object or array.
• Uses a dot-notation path syntax with $ as the root.
• OPENJSON() can shred JSON into a relational rowset.
• JSON_PATH_EXISTS() was added in SQL Server 2022.

SELECT
  json_extract(payload, '$.type') AS event_type,
  payload ->> '$.type' AS event_type_alt,
  json_extract(payload, '$.x') AS x_value
FROM events
WHERE json_extract(payload, '$.type') = 'click';

• json_extract() is the path extraction function.
• The -> and ->> operators were added in SQLite 3.38.0 (2022-02-22).
• -> returns a JSON value; ->> returns the unwrapped SQL value.

SELECT
  json_extract_path_text(payload, 'type') AS event_type,
  json_extract_path_text(payload, 'address', 'city') AS city
FROM events;

• json_extract_path_text(json_col, 'path_elem1', 'path_elem2') for nested extraction.
• json_extract_path(json_col, ...) returns SUPER type.
• No JSONPath ($.path) syntax - path elements are passed as separate string arguments.
• PartiQL on SUPER data provides native path navigation even though the syntax differs from SQL/JSON path.

SELECT
  JSON_VALUE(payload, '$.type' RETURNING VARCHAR(100)) AS event_type,
  JSON_QUERY(payload, '$.address') AS address_obj
FROM events
WHERE JSON_EXISTS(payload, '$.type');

• JSON_VALUE(col, '$.path' RETURNING type), JSON_QUERY(col, '$.path').
• JSON path query functions added in DB2 11.1.
• Uses SQL/JSON path syntax.
• JSON_EXISTS() for testing path existence.

SELECT
  data:user:name::STRING AS username,
  data['address']['city']::STRING AS city,
  GET_PATH(data, 'user.age')::INT AS age
FROM events;

• Colon operator is idiomatic Snowflake: col:key or col:key::type for type casting.
• GET_PATH(col, 'key.subkey') and JSON_EXTRACT_PATH_TEXT(col, 'key') also available.
• Bracket notation: col['key'].
• Supports nested access: col:address:city::STRING.
• Type casting with ::STRING, ::INT, ::FLOAT.

SELECT
  JSON_VALUE(payload, '$.type') AS event_type,
  JSON_QUERY(payload, '$.address') AS address_obj,
  JSON_VALUE_ARRAY(payload, '$.tags') AS tags
FROM events;

• JSON_VALUE(col, '$.path') for scalar values, JSON_QUERY(col, '$.path') for JSON fragments.
• Dot notation for JSON type columns.
• The JSON type (native, v2022) supports direct dot notation.
• For STRING-stored JSON, use JSON_VALUE/JSON_QUERY.
• JSON_VALUE_ARRAY and JSON_QUERY_ARRAY for array extraction.