Python: Retrieving PostgreSQL query results as formatted JSON values

Question

I am working with a postgres database containing several tables. The goal is to retrieve a formatted JSON from query results obtained. I created this python script fetching dataset from table(test case) so as to manipulate the query results:

import psycopg2
import json
from time import sleep
from config import config

def main():
    conn = None
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute("select * from location")
        row = cur.fetchone()

        while row is not None:
            print(row)
            #do field rename, merge(log, lat) and obtained JSON here
            sleep(0.3)
            row = cur.fetchone()

        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

if __name__ == '__main__':
    main()

To make my question clear, I produce here a simplified scenario representing the task at hand with 3 tables as shown below.

environment
╔════════╦═══════════╦══════╦══════╗
║ env_id ║ placem_id ║ humd ║ wind ║
╠════════╬═══════════╬══════╬══════╣
║     104║        4  ║   48 ║  119 ║
║     68 ║        9  ║   39 ║  141 ║
╚════════╩═══════════╩══════╩══════╝

placement
╔═══════════╦════════╦═══════════════╦══════════════════════════╗
║ placem_id ║ loc_id ║  description  ║           date           ║
╠═══════════╬════════╬═══════════════╬══════════════════════════╣
║         4 ║     64 ║ description_1 ║ 2019-03-12T20:40:35.967Z ║
║         7 ║      5 ║ description_2 ║ 2019-03-12T20:56:51.319Z ║
╚═══════════╩════════╩═══════════════╩══════════════════════════╝

location
╔════════╦═══════════╦═══════════╦════════════════════╗
║ loc_id ║    log    ║    lat    ║      address       ║
╠════════╬═══════════╬═══════════╬════════════════════╣
║     64 ║ 13.3986   ║ 52.5547   ║ Bosebrucke Einkauf ║
║     71 ║ 21.150122 ║ -6.607044 ║ Charlotte Court    ║
╚════════╩═══════════╩═══════════╩════════════════════╝

Here's what I would want to achieve:

retrieve records from database
rename some of the field as appropriate in the JSON name/value (e.g. humd to become relativeHumidity and windto windSpeed
merge log and lat fields to single JSON value like coordinate[log,lat]

So that the JSON returned is in the form:

{
    "relativeHumidity": 48,
    "windSpeed": 119,
    "address": "Bosebrucke Einkauf",
    "location": {
        "coordinates": [13.3986, 52.5547]
    }
}

While this question might seems a duplicate, however, I tried many suggestions pointed in similar questions such as the one here but none of these actually work.

Can anyone out there offer a guide please?

roman · Accepted Answer · 2019-04-19 07:37:47Z

2

I think it should be relatively easy to do with functions-json:

select
    json_agg(to_json(d))
from (
    select
        e.humd as "relativeHumidity",
        e.wind as "windSpeed",
        l.address,
        json_build_object(
            'coordinates',
            json_build_array(l.log, l.lat)
        ) as location
    from environment as e
        inner join placement as p on
            p.placem_id = e.placem_id
        inner join location as l on
            l.loc_id = p.loc_id
) as d

db<>fiddle demo

answered Apr 19, 2019 at 7:37

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

arilwan Over a year ago

many thanks for this piece, short and precise. It works in deed. My first time coming across this db fiddle.

arilwan Over a year ago

suppose I want each record to be returned as single JSON object instead of whole query results as single JSON, (say I want each record every 1sec). How do I modify above query please. Working on it but not successful (writing a python script).

roman Over a year ago

just remove json_agg - dbfiddle.uk/…

Collectives™ on Stack Overflow

Python: Retrieving PostgreSQL query results as formatted JSON values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related