Thread: [GENERAL] Questions regarding JSON processing
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
2017-04-26 6:21 GMT+02:00 Glen Huang <heyhgl@gmail.com>:
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?
The PLpgSQL is static language and is good for static processing JSON doc, but it is unfit for iteration over any generic nested document. You can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.
The functions in PostgreSQL are executed under transaction - you cannot to explicitly control transaction, but there are possibility to implicitly handle transactions with exception handling. There is workaround via dblink to emulate autonomous transactions.
Regards
Pavel
Would like to hear some thoughts on this. Thanks.Glen
On 4/25/2017 9:21 PM, Glen Huang wrote: > 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? > what does "walk a JSON structure and run a transaction along the way" actual entail? Normally, the application starts a transaction, executes one or more SQL commands, then does a commit or rollback. any JSON would be within this transaction. functions are called within a transaction. If your JSON includes instructions as well as data, I'd be rather cautious of letting a remote client send that directly to the database server unless you can ensure that nothing hostile can be done with it, or completely trust all clients. -- john r pierce, recycling bits in santa cruz
@Pavel
Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the right tool to for the job. I'll try it out. Do you think it makes sense to use PLV8 to also generate JSON? Can it beat SQL?
Good to know functions are executed under transaction, I think that should be enough for me.
@John
Only data is inside JSON, but it does have keys like "added", "updated" that contain objected to be added and updated inside it. I think this kind of branching should be safe though?
On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2017-04-26 6:21 GMT+02:00 Glen Huang <heyhgl@gmail.com>: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?The PLpgSQL is static language and is good for static processing JSON doc, but it is unfit for iteration over any generic nested document. You can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.The functions in PostgreSQL are executed under transaction - you cannot to explicitly control transaction, but there are possibility to implicitly handle transactions with exception handling. There is workaround via dblink to emulate autonomous transactions.RegardsPavelWould like to hear some thoughts on this. Thanks.Glen
2017-04-26 15:06 GMT+02:00 Glen Huang <heyhgl@gmail.com>:
@PavelThanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the right tool to for the job. I'll try it out. Do you think it makes sense to use PLV8 to also generate JSON? Can it beat SQL?
Hard to say - probably it depends on actual case. I have not any benchmarks.
Regards
Pavel
Good to know functions are executed under transaction, I think that should be enough for me.@JohnOnly data is inside JSON, but it does have keys like "added", "updated" that contain objected to be added and updated inside it. I think this kind of branching should be safe though?On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:2017-04-26 6:21 GMT+02:00 Glen Huang <heyhgl@gmail.com>: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?The PLpgSQL is static language and is good for static processing JSON doc, but it is unfit for iteration over any generic nested document. You can use PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.The functions in PostgreSQL are executed under transaction - you cannot to explicitly control transaction, but there are possibility to implicitly handle transactions with exception handling. There is workaround via dblink to emulate autonomous transactions.RegardsPavelWould like to hear some thoughts on this. Thanks.Glen