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