Skip to content

Missing support for some Postgres operators #1236

@ReppCodes

Description

@ReppCodes

We're seeing parse failures on some of Postgres' json operators. I've copied the example queries from the pg docs into a test that would have caught it. Any objections to me adding support for these?

#[test]
fn parse_json_ops() {
    // Does the left JSON value contain the right JSON path/value entries at the top level?
    let sql = r#"SELECT '{"a":1, "b":2}'::JSONB @> '{"b":2}'::JSONB"#;
    pg().verified_stmt(sql);

    // Are the left JSON path/value entries contained at the top level within the right JSON value?
    let sql2 = r#"SELECT '{"b":2}'::JSONB <@ '{"a":1, "b":2}'::JSONB"#;
    pg().verified_stmt(sql2);

    // FAILS
    // Does the string exist as a top-level key within the JSON value?
    let sql3 = r#"SELECT '{"a":1, "b":2}'::JSONB ? 'b'"#;
    pg().verified_stmt(sql3);

    // FAILS
    // Do any of these array strings exist as top-level keys?
    let sql4 = r#"SELECT '{"a":1, "b":2, "c":3}'::JSONB ?| array['b', 'c']"#;
    pg().verified_stmt(sql4);

    // FAILS
    // Do all of these array strings exist as top-level keys?
    let sql5 = r#"SELECT '["a", "b"]'::JSONB ?& array['a', 'b']"#;
    pg().verified_stmt(sql5);

    // Concatenate two JSONB values into a new JSONB value
    let sql6 = r#"SELECT '["a", "b"]'::JSONB || '["c", "d"]'::JSONB"#;
    pg().verified_stmt(sql6);

    // Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key value.
    let sql7 = r#"SELECT '{"a": "b"}'::JSONB - 'a'"#;
    pg().verified_stmt(sql7);

    // Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.
    let sql8 = r#"SELECT '["a", "b"]'::JSONB - 1"#;
    pg().verified_stmt(sql8);

    // Delete the field or element with specified path (for JSON arrays, negative integers count from the end)
    let sql9 = r#"SELECT '["a", {"b":1}]'::JSONB #- '{1,b}'"#;
    pg().verified_stmt(sql9);
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions