CODEWITHSUNDEEP
sqljsonamazon-redshift
Tamizh Selvan Kandasamy
Tamizh Selvan Kandasamy

Reputation: 33

Convert tabular data to json from redshift - Select query to fetch the records in json format

For example,

Table sample

id name
1  abc
2  def

I know in sql server there is something called "select * from sample For JSON auto" to fetch the records and output in json format. Is there anything similar kind of thing in redshift ?

sample output:

[{ 'id':'1', 'name':'abc' }, { 'id':'2', 'name':'def' }]

Upvotes: 1

Views: 715

Answers (1)

alex
alex

Reputation: 255

How about something like this? This works by using the postgres cli psql to output to csv and then using python's built in csv and json libraries to parse it.

pql --csv -c 'select 1 as a, 2 as b union all select 3 as a,4 as b'|python3 -c 'import csv; import sys; import json; s=sys.stdin; d=csv.DictReader(s.readlines()); print(json.dumps(list(d)))'|jq

Which outputs this:

[
  {
    "a": "1",
    "b": "2"
  },
  {
    "a": "3",
    "b": "4"
  }
]

pql here is a zsh alias I use that is nothing more than this:

pql () {
    local PSWD=$(cat <password file here>)
    local DSN="redshift DSN ... password=${PSWD} ..."
    psql "${DSN}" "$@"
}

Pre-Reqs:

Also you'll need jq which can be installed by most package managers and brew if on MacOS.

Python -- the example uses Python 3.

PSQL -- the cli front end to Postgres, installable by most package managers. On Mac for example, using HomeBrew it's brew install Postgres@12 -- there are a number of versions available.

Upvotes: 2

Related Questions