Questions regarding JSON processing

From: Glen Huang <heyhgl(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Questions regarding JSON processing
Date: 2017-04-26 04:21:01
Message-ID: CALNXhk0oJdf5SL=K7xRFiF8AjHhFvf+UCouLaUG6MDf6Vew7oQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I have a RESTful API server that sends and receives JSON strings. I'm
wondering what might be the best way to leverage PostgreSQL's JSON
capability.

For sending JSON responses to clients. I believe the best way is to ask
PostgreSQL to generate the JSON string and then pass that directly to
clients, instead of making multiple queries to construct the JSON and then
send it, which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure.
Should I directly pass the request JSON to PostgreSQL and ask it to parse
this JSON and execute a transaction all by itself, or should I parse it in
the server and generate the transaction SQL and execute that on PostgreSQL?
The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
JSON structure and run a transaction along the way? Should I do it with
PL/pgSQL? It seems functions can't execute a transaction?

Would like to hear some thoughts on this. Thanks.

Glen

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2017-04-26 04:41:22 Re: Questions regarding JSON processing
Previous Message Andy Colson 2017-04-25 19:33:29 Re: pg_test_fsync performance