Postgre Convert column jsonb in another table where the columns are the keys

Question

I need to create an script using python in this case to take a column jsonb of one table an create another table where the columns are all possible keys of this of this json.

For instance:

From

id  | optional

1   | {"a":"4", "b":"5"}
2   | {}
3   | {"a":"8", "c":"9", "d":"9"}

to

id  |  a   |   b   |  c  |  d

1   |  4   |   5   |     |  
3   |  8   |       |  9  |  9

I got the keys using this query:

select jsonb_object_keys(optional) as key from table group by key

I use the following code in python to create a table with keys as columns

    connection = psycopg2.connect( host=host, database=database, user=user, password=password)
    try:      
        columns = "("
        for column in keys:
            columns+=column+" TEXT "+','
        columns = columns[0:len(columns)-1]
        columns += ");"
        query = "CREATE TABLE " + table +" "
        query +=  columns
        print query
        cur = connection.cursor()
        cur.execute(query)
        connection.commit()
        cur.close()

and I got the data that I need to put in the other table using this query:

select id, optional->'a',...  from table where optional<>'{}'

In my case I have around 31 keys so the query above is big and on other hand if I want to reuse this script to another case I need to change this query probably.

So I would like to know if there are another way more elegant and more generic to do that. Even it is not necessary that the solution uses python if it is only with postgres it is good for me too

Any idea?

Thanks in advance

klin · Accepted Answer · 2017-09-06T01:28:15.990

4

You may be insterested in Postgres solution described in this answer (see Generalized solution).

Example source table:

drop table if exists my_table;
create table my_table(id int primary key, data jsonb);
insert into my_table values
(1, '{"a":"4", "b":"5"}'),
(2, '{}'),
(3, '{"a":"8", "c":"9", "d":"9"}');

Using the function:

select create_jsonb_flat_view('my_table', 'id', 'data');

select * from my_table_view;

 id | a | b | c | d 
----+---+---+---+---
  1 | 4 | 5 |   | 
  2 |   |   |   | 
  3 | 8 |   | 9 | 9
(3 rows)

You can create a new table based on the flat view:

create table my_new_table as
select *
from my_table_view
order by id;

edited Sep 06 '17 at 01:28

answered Sep 05 '17 at 23:32

klin

112,967
15
204
232

Soory But I have this error when I try to execute your code SQL error: `ERROR: function create_jsonb_flat_view(unknown, unknown, unknown) does not exist LINE 1: select create_jsonb_flat_view('my_table', 'id', 'data'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.` – Cyberguille Sep 05 '17 at 23:46
1

You have the code of the function in the post. You should create the function (once) in your database. – klin Sep 05 '17 at 23:49
Just need to create a table with the keys like a columns, but I don't know how to to that generic `select id, optional->'a',... `, something like put `select id, optional->keys,... `, and no need to specify the name of the column – Cyberguille Sep 06 '17 at 01:04
You can always create a new table based on the created view, see the updated answer. In most cases a view may be more convenient than a new table. Of course, your needs may be specific. – klin Sep 06 '17 at 01:27
Yes I understand that, what I mean is that now I need to put the query `select id, optional->'a',optional->'b',optional->'c',...` I would like to use this script in other tables, and I don't want put manually the keys in the select, Is there any way to do this since postgresql only, I know how to do that using python we got the keys so we can build the query and we don't need to put in the keys manually for every case that I use this script – Cyberguille Sep 06 '17 at 02:26
The only way in plain SQL is `select *` – klin Sep 06 '17 at 02:47
So I can Select all existing json fields from a postgres table – Cyberguille Sep 06 '17 at 03:22
Thanks for your help, with respect to the last issue that I asked you I posted a question on Database Adminatrator [PostgreSQL converting undefined JSONB fields into a row dynamically](https://dba.stackexchange.com/questions/185242/postgresql-converting-undefined-jsonb-fields-into-a-row-dynamically) – Cyberguille Sep 06 '17 at 21:18

Postgre Convert column jsonb in another table where the columns are the keys

1 Answers1