CODEWITHSUNDEEP
pythonsqlsnowflake-cloud-data-platform
John Wick
John Wick

Reputation: 745

Function to return results of a snowflake query in the form of a json object

Let's say I have a query that I am running in SNOWFLAKE:

select years from date;

Which returns a data set that looks like this:

1984
1985
1986
....
2019
2020
2021

I am trying to return a JSON object that looks like this:

["m-1980", "m-1981"..., "m-2021"]

Is my function missing something?

import psycopg2
import json
import snowflake.connector as sf

def get_lyear():
    orig = sf.cursor()          #set the cursor object
    orig.execute('''select distinct year from schema.years''')
    row_headers=[x[0] for x in orig.description] #extract headers
    orig_json = orig.fetchall()
    converted_data=[]
    for i in orig_json:
        converted_data.append(dict(zip(rowheaders,i)))
    return json.dumps(converted_data)

Thanks in advance.

Upvotes: 0

Views: 1592

Answers (2)

DV-168516
DV-168516

Reputation: 11

I would rather modify the proposed solution to define the output column size after doing the cast year as string:

SELECT ARRAY_AGG(DISTINCT 'm-' || years::TEXT**(6)**) AS res
FROM date

or

SELECT ARRAYAGG('m-' || years::TEXT**(6)**) WITHIN GROUP(ORDER BY years) AS res
FROM (SELECT DISTINCT years FROM dates);

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

Using ARRAY_AGG:

SELECT ARRAY_AGG(DISTINCT 'm-' || years::TEXT) AS res
FROM date

or:

SELECT ARRAYAGG('m-' || years::TEXT) WITHIN GROUP(ORDER BY years) AS res
FROM (SELECT DISTINCT years FROM dates);

Output:

enter image description here

Upvotes: 2

Related Questions