How to create a set of record from a set of text?

Question

I have this code json_object_keys('{"A": 1, "B": 0, "C": 5}'::json) which returns a set of text like this:

json_object_keys
----------------
| A |
-----
| B |
-----
| C |
-----

Based on this json_object_keys result, I want to create a temporary recordset or table to loop through these to perform update to a table column where its equal to each key. So how do I create a recordset?

Answer 1

Usually you do not need to explicitly create a temporary table, because you can use the IN (subquery) expression, example:

update my_table
set some_column = some_value
where key_column in (
    select json_object_keys('{"A": 1, "B": 0, "C": 5}'::json));

In fact, the server creates a temporary recordset in memory from the subquery during the execution of the query.

However, if you do need a named temporary table, use CREATE TABLE AS:

create temporary table my_temp_table as
select json_object_keys('{"A": 1, "B": 0, "C": 5}'::json);