Function postgreSQL with JSON parameters

Question

I have to create a function that takes an object of this type :

    "users":"John",
    "Level":"1",
    "list":[
      {"id":"1", "price1":"100.50", "price2":"90.50"},
      {"id":"2", "price1":"100.60", "price2":"80.50"},
      {"id":"2", "price1":"105.50", "price2":"700.50"}
    ]}

For information JSON is not obliged but it is the thing that seemed the simplest to me. But maybe the creation of a POSTGRES type can work.

Then how to process the object if I use a JSON or PostGres Type.

    CREATE OR REPLACE FUNCTION MYFUNCTION(myobject JSON ? CREATE TYPE ? )
      RETURNS TABLE (
        "OK" BOOLEAN,
        "NB" VARCHAR
      )
    
    AS $$
    
    DECLARE
    
    
    BEGIN
    
    -- I would like to get for each object in the list the price1 and price2 to 
       compare them. 
    
    END; $$
    
    LANGUAGE 'plpgsql';

Answer 1

The crux of the question seems to be how to extract the values from the json object. This is one way:

select * from json_to_recordset('{
     "users":"John",
     "Level":"1",
     "list":[
      {"id":"1", "price1":"100.50", "price2":"90.50"},
      {"id":"2", "price1":"100.60", "price2":"80.50"},
      {"id":"2", "price1":"105.50", "price2":"700.50"}
    ]}'::json->'list') as foo(id int, price1 numeric, price2 numeric);

With a json variable instead of the literal string:

select * from json_to_recordset(jsonvariable->'list') as foo(id int, price1 numeric, price2 numeric)

Note. The json object you provide isn't legal. Some commas missing. I'd also suggest you use jsonb instead of json.

Edited:This is a skeleton on how you can use this in a plpgsql function:

create or replace function func(jsonvariable json) returns table (ok boolean, nb text) as 
$BODY$
declare 
    r record;
begin
    for r in (select * from json_to_recordset(jsonvariable->'list') as foo(id int, price1 numeric, price2 numeric)) loop
        --- DO THINGS
        ok:=(r.price1>r.price2);
        nb:='this is text returned';
        return next;
    end loop;
end;
$BODY$
language plpgsql;