PostgreSQL json Columns

13 views
Skip to first unread message

jgt...@gmail.com

unread,
Sep 28, 2020, 3:46:31 PM9/28/20
to peewee-orm
Hi 
I am working on  PostgreSQL  with a verticalized table 

table1
_____________________________________
| id | client | description | value               |
| 1  |     1    |  email           | c...@mail.com  |
| 2  |     1    |  name           | user1              |
| 3  |     2    |  email           | a...@mail.com  |
| 4  |     2    |  name           | user2              |

Select client jsonb_object_agg(description,value) as val from table1 

this query on  PostgreSQL   return 
____________________________________________
| 1 | {"email":" c...@mail.com","name":"user1" }  |
| 2 | {"email":" a...@mail.com","name":"user2" }  |

the first column is an int and the second  is a Json 

but with pewee

data = table1.select(table1.client ,
                   fn.jsonb_object_agg( table1.description,
                                                          table1.value).alias('val'))
                   .group_by(table1.client ).dicts().first()

return 
[
  {  'client' : 1 , 'val' : '{"email":" c...@mail.com","name":"user1" } '},
  {  'client' : 2 , 'val' : '{"email":" a...@mail.com","name":"user2" } '},
]

so the problem is that  val is an string instead of a JSON does  exist a way to  change the type of the  column val to JSON  I tryed to use cast but I have the same result a string with the Json 

Charles Leifer

unread,
Sep 29, 2020, 8:41:48 AM9/29/20
to peewe...@googlegroups.com
Yes, you can specify the converter for a column that is a function call:

obj = (fn
       .jsonb_object_agg(table1.description, table1.value)
       .python_value(json.loads)
       .alias('val'))

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/812d78f7-f2fe-492a-89fb-2de14284cca9n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages