Support Using JSONB Fields Directly in Functions

[awgneo]

trafficstars

What version of GraphJin are you using? graphjin version

v3.0.38

Have you tried reproducing the issue with the latest release?

Yes

What is the hardware spec (RAM, OS)?

32GB M1 Max, MacOS Sequoia 15.0

Steps to reproduce the issue (config used to run GraphJin).

I think this might be a simple fix :)

I haven't tried this with tables, yet; however, with the Postgres function:

CREATE FUNCTION agreego.alexfunc (IN obj jsonb)
RETURNS TABLE (output_var text, existed boolean)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Extract the field 'field_name' from the input jsonb and return it
    RETURN QUERY
    SELECT obj->>'field' AS output_var, true AS existed;
END;
$$;

I am unable to run this GraphQL query:

query Alexfunc {
    alexfunc(args: { obj: { field: "Alex" } }) {
        output_var
    }
}

The SQL statement generated is:

"/* action='Alexfunc',controller='graphql',framework='graphjin' */ SELECT jsonb_build_object('alexfunc', __sj_0.json) AS __root FROM ((SELECT true)) AS __root_x LEFT OUTER JOIN LATERAL (SELECT COALESCE(jsonb_agg(__sj_0.json), '[]') AS json FROM (SELECT to_jsonb(__sr_0.*) AS json FROM (SELECT \"alexfunc_0\".\"output_var\" AS \"output_var\" FROM (SELECT \"alexfunc\".\"output_var\" FROM alexfunc(obj => '') AS \"alexfunc\" LIMIT 20) AS \"alexfunc_0\") AS \"__sr_0\") AS \"__sj_0\") AS \"__sj_0\" ON true"

The error I receive is:

{
    "errors": [
        {
            "message": "ERROR: invalid input syntax for type json (SQLSTATE 22P02)"
        }
    ]
}

And you can see alexfunc(obj => '') with nothing being passed in. When attempting with the following:

query Alexfunc {
    alexfunc(args: { obj: "{ \"field\": \"Alex\" }" }) {
        output_var
    }
}

Basically, trying as a string JSON object, the SQL statement generated is:

"/* action='Alexfunc',controller='graphql',framework='graphjin' */ SELECT jsonb_build_object('alexfunc', __sj_0.json) AS __root FROM ((SELECT true)) AS __root_x LEFT OUTER JOIN LATERAL (SELECT COALESCE(jsonb_agg(__sj_0.json), '[]') AS json FROM (SELECT to_jsonb(__sr_0.*) AS json FROM (SELECT \"alexfunc_0\".\"output_var\" AS \"output_var\" FROM (SELECT \"alexfunc\".\"output_var\" FROM alexfunc(obj => '{ \\\"field\\\": \\\"Alex\\\" }') AS \"alexfunc\" LIMIT 20) AS \"alexfunc_0\") AS \"__sr_0\") AS \"__sj_0\") AS \"__sj_0\" ON true"

Where now everything is present, but being double escaped alexfunc(obj => '{ \\\"field\\\": \\\"Alex\\\" }'), I believe, by:

https://github.com/dosco/graphjin/blob/a81023af7947f15da32d050cd6f72c301a0eace6/core/internal/psql/fn.go#L96

But I think at this point a.Val is already over-escaped and the error is the same.

Expected behaviour and actual result.

I would love it if I could use args: { obj: { field: "Alex" } } and not have to pass in a JSON string to set a JSONB field, but passing a JSON string would be an acceptable backup. I don't need anything as heavy handed as JSONB virtual tables, but the ability to serialize JSONB in and out of GraphQL, and specifically postgres functions, would be awesome.