Thread: json results parsing

On Wed, May 30, 2018 at 08:16:18PM -0700, Charles Cui wrote:
> Basically, I want to call these json API inside a plugin, and the return
> value for these APIs are Datum. I am wondering how to parse a set of json
> results from Datum in postgresql? Seems the helper functions I am aware of
> are only for simple types. Are there any examples to demonstrate the best
> practice of how to do that?

When it comes to the manipulation of data which uses a given data type,
you may want to look at how the input and output functions of this data
type work to understand how they can be fetched and saved.  In the case
of json, that would be looking at respectively json_in and json_out.
Each type (and its array representation) has its own row within the
system catalog pg_type, where the input function is marked with
typinput, and the output as typoutput.

I hope that helps.
--
Michael

>>>>> "Charles" == Charles Cui <charles.cui1984@gmail.com> writes:

 Charles> Hi mentors and hackers,
 Charles>    Found the json API page here
 Charles> https://www.postgresql.org/docs/9.3/static/functions-json.html
 
 Charles> For some APIs, it returns set of results, for example,
 Charles> json_each(json) and json_object_keys().
 
 Charles> Basically, I want to call these json API inside a plugin,

If you want to process the whole of a json (not jsonb) value inside a
plugin, then maybe pg_parse_json is what you should be looking at rather
than the SQL-callable functions.

For jsonb, there's an iterator interface (JsonbIteratorInit/JsonbIteratorNext)

-- 
Andrew (irc:RhodiumToad)

>>>>> "Charles" == Charles Cui <charles.cui1984@gmail.com> writes:

 Charles> Thanks you guys for answering my questions, and sorry for confusion.
 Charles> To clarify, I think my question is functions like json_each or
 Charles> json_object_keys() are
 Charles> set returning functions (
 Charles> https://www.postgresql.org/docs/9.5/static/functions-srf.html),
 Charles> which means it returns a set of results into a Datum.

No. Set-returning functions follow a somewhat complex call protocol that
may involve the function being called multiple times (returning one row
each time as a composite Datum) or may involve the function being called
once and returning a tuplestore (not as the return value but via the
ReturnSetInfo struct).

I would not try calling a SRF directly from C code without going through
SPI (i.e. setting up and running an actual query from inside your
function).

I definitely think you should be looking at pg_parse_json instead.

-- 
Andrew (irc:RhodiumToad)

On 05/31/18 20:20, Charles Cui wrote:
> To clarify, I think my question is functions like json_each or
> json_object_keys() are
> set returning functions (
> https://www.postgresql.org/docs/9.5/static/functions-srf.html),
> which means it returns a set of results into a Datum.

Well, it returns one result row as a Datum (either a single value
or a tuple) each time it is called, until the whole set has been
returned. The process is described here

https://www.postgresql.org/docs/9.5/static/xfunc-c.html#XFUNC-C-RETURN-SET

(though that description is from the point of view of the set-returning
function being called, not the point of view of its caller).

-Chap

>>>>> "Chapman" == Chapman Flack <chap@anastigmatix.net> writes:

 >> To clarify, I think my question is functions like json_each or
 >> json_object_keys() are
 >> set returning functions (
 >> https://www.postgresql.org/docs/9.5/static/functions-srf.html),
 >> which means it returns a set of results into a Datum.

 Chapman> Well, it returns one result row as a Datum (either a single
 Chapman> value or a tuple) each time it is called, until the whole set
 Chapman> has been returned. The process is described here

Unfortunately that describes only one of the two calling protocols for
SRFs - the value-per-call mode, which as it happens is NOT the one that
json_each uses; that requires materialize mode, which is not actually
covered in the docs (but you can find examples in contrib/tablefunc).

-- 
Andrew (irc:RhodiumToad)