Removing field from json and casting it to jsonb-CodePudding

I want to remove field from array of jsons and then retreive that array as jsonb object. I am able to remove field. Now I want to have single object to return from function.

The way it is now I get

ERROR: more than one row returned by a subquery used as an expression

BEGIN
    RETURN (
        WITH records (game_numbers) AS (
            SELECT *
            FROM jsonb_array_elements($1))

       SELECT (game_numbers - 'code')::json
        FROM records);

END;
$$;

I have data like:

"game_numbers":[
                  {
                    "id":1,
                    "code":"code1"
                  },
                  {
                    "id":2,
                    "code": "code2"
                  }

               ]

I want to receive:

"game_numbers":[
                  {
                    "id":1,
                  },
                  {
                    "id":2,
                  }

               ]

CodePudding user response：

If you want the function to return a jsonb, then you need to aggregate the elements back to a jsonb.

CREATE FUNCTION fnRemoveCodeElement(jsonb)
RETURNS jsonb AS $$
    SELECT json_agg(value - 'code')::jsonb
    FROM jsonb_array_elements($1)
$$ LANGUAGE SQL;

SELECT fnRemoveCodeElement('[
{
  "id":1,
  "code":"code1"
},
{
  "id":2,
  "code": "code2"
}
]') js

js

[{"id": 1}, {"id": 2}]

js
[{"id": 1}, {"id": 2}]

db<>fiddle here