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_in TEXT, keys_types_in TEXT)
RETURNS JSONB AS
$$
DECLARE
keys TEXT[] := string_to_array(keys_in, ',');
keys_types TEXT[] := string_to_array(keys_types_in, ',');
val TEXT := '';
r TEXT;
c INT := 1;
BEGIN
FOREACH r IN ARRAY keys
LOOP
val = val || '''' || r || ''', ''' || CASE keys_types[c]
WHEN 'TEXT' THEN randomstring(randomint(5, 10))::TEXT
WHEN 'INT' THEN randomint(1, 1000)::TEXT
WHEN 'DATE' THEN randomdate(current_date-1000, current_date+1000)::TEXT
WHEN 'DEC' THEN randomdec(100, 1000)::TEXT
ELSE ''
END || ''', ';
c = c + 1;
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 two parameters are accepted:
- A comma separated list of keys
- A comma separated list of data types for each of the keys provided in the first parameter
The supported data types are TEXT, INT, DATE and DEC.
Example:
yugabyte=# select randomjsonb('a,b,c,d', 'TEXT,INT,DATE,DEC');
randomjsonb
---------------------------------------------------------------------------
{"a": "neldjnfL", "b": "714", "c": "2023-06-09", "d": "101.862336089835"}
(1 row)
yugabyte=# select randomjsonb('first_name,last_name,hire_date,dept_id,base_salary', 'TEXT,TEXT,DATE,INT,DEC');
randomjsonb
---------------------------------------------------------------------------------------------------------------------------------
{"dept_id": "934", "hire_date": "2021-02-07", "last_name": "YTbUTm", "first_name": "juTOBi", "base_salary": "501.654003513977"}
(1 row)
Have Fun!
