Whenever you are populating table columns with dummy data you’ll probably need to generate some random JSONB strings.
Although there is not a built in function for this, we can create our own using a set of other random functions we created in previous tips.
Here are links to the previous tips that include the code for each of the random functions we’ll use in the random JSONB function:
Shown below is the code for a new random function that will return a JSONB string.
CREATE OR REPLACE FUNCTION randomjsonb(keys INT)
RETURNS JSONB AS
$$
DECLARE
key_type TEXT[];
val TEXT := '';
BEGIN
FOR loop_cnt IN 1..keys LOOP
key_type[loop_cnt] = CASE randomint(1, 4)::TEXT WHEN '1' THEN 'randomstring(randomint(5, 10))' WHEN '2' THEN 'randomint(1, 1000)' WHEN '3' THEN 'randomdate(current_date-1000, current_date+1000)' WHEN '4' THEN 'randomdec(100, 1000)' END;
END LOOP;
FOR loop_cnt IN 1..keys LOOP
val = val || '''' || LOWER(randomstring(randomint(5, 10))) || ''', ' || key_type[loop_cnt] || ', ';
END LOOP;
val = RTRIM(val, ', ');
val = 'SELECT jsonb_build_object(' || val || ');';
EXECUTE val INTO val;
RETURN val;
END;
$$ language 'plpgsql';
In this version of the code, the number of keys added the JSONB string is the integer value passed to the function, the JSONB key names are a random lower case string of 5-10 characters, and the key values can be a string, date, integer or decimal.
Example:
yugabyte=# SELECT randomjson(3);
randomjson
----------------------------------------------------------------------------
{"bxhmp": 812.042861338705, "uruui": "2022-12-15", "xylbxtwo": "VPYApRFt"}
(1 row)
yugabyte=# SELECT randomjson(7);
randomjson
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"bvset": "CuksBeD", "unkov": "HhETJ", "vmbbpfx": 268.630078434944, "ucdkuifg": 345.673597883433, "pqgtkvjek": "2023-10-04", "ptnjqdigd": 942, "iimrmswhgh": "2021-11-29"}
(1 row)
Have Fun!
