psql_client output as json array

[Vincent_Bacher]

Hi mates,

I already mentioned it in a thread, but I guess not everyone saw it here. So here's a new thread for your information:

Perhaps some of you weren't aware that the output of psql_client can also be displayed as a JSON array, which makes it perfect for use in scripts.

Here's an example of my last admin sessions on my lab management.

best
Vince

psql_client cpm postgres -t -c "SELECT json_agg(row_to_json(t)) FROM (SELECT adminname, ip, time, applicationname FROM lastloginrecord WHERE applicationname = 'SmartConsole' AND adminname = 'vince') t;" | jq .
[
  {
    "adminname": "vince",
    "ip": "10.10.10.1",
    "time": "2026-02-19T08:01:09.997",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.2",
    "time": "2026-02-17T12:42:16.392",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.3",
    "time": "2026-02-17T12:42:42.942",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.1",
    "time": "2026-02-13T10:03:48.345",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.5",
    "time": "2026-02-17T12:43:53.613",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.6",
    "time": "2026-02-13T10:03:53.095",
    "applicationname": "SmartConsole"
  },
  {
    "adminname": "vince",
    "ip": "10.10.10.1",
    "time": "2026-02-19T07:37:35.2",
    "applicationname": "SmartConsole"
  }
]

[PhoneBoy]

Today, I learned 🙂
That's quite useful, actually, because parsing JSON is more straightforward.

[the_rock]

Super useful!