Skip to content

Missing support for some Postgres operators #1236

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
ReppCodes opened this issue Apr 26, 2024 · 0 comments · Fixed by #1242
Closed

Missing support for some Postgres operators #1236

ReppCodes opened this issue Apr 26, 2024 · 0 comments · Fixed by #1242

Comments

@ReppCodes
Copy link
Contributor

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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant