Re: Questions regarding JSON processing

2017-04-26 15:06 GMT+02:00 Glen Huang <heyhgl(at)gmail(dot)com>:

> @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?
>

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.
>
> @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(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 2017-04-26 6:21 GMT+02:00 Glen Huang <heyhgl(at)gmail(dot)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
>>>
>>
>>
>