Add JSON Processing Functions

[t9t]

https://www.postgresql.org/docs/11/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

It would be very nice to also add JSON processing functions, besides the operators.

• json_array_length(json)
• jsonb_array_length(jsonb)
• json_each(json)
• jsonb_each(jsonb)
• json_each_text(json)
• jsonb_each_text(jsonb)
• json_extract_path(from_json json, VARIADIC path_elems text[])
• jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
• json_extract_path_text(from_json json, VARIADIC path_elems text[])
• jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
• json_object_keys(json)
• jsonb_object_keys(jsonb)
• json_populate_record(base anyelement, from_json json)
• jsonb_populate_record(base anyelement, from_json jsonb)
• json_populate_recordset(base anyelement, from_json json)
• jsonb_populate_recordset(base anyelement, from_json jsonb)
• json_array_elements(json)
• jsonb_array_elements(jsonb)
• json_array_elements_text(json)
• jsonb_array_elements_text(jsonb)
• json_typeof(json)
• jsonb_typeof(jsonb)
• json_to_record(json)
• jsonb_to_record(jsonb)
• json_to_recordset(json)
• jsonb_to_recordset(jsonb)
• json_strip_nulls(from_json json)
• jsonb_strip_nulls(from_json jsonb)
• jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
• jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])
• jsonb_pretty(from_json jsonb)

I should probably focus on the ones that return json, jsonb, and text first as they will be very simple. The ones that return more complex types (such as setof text or record) will be much more difficult, so I will do them later (or even not at all, if it proves to be too time consuming).

[t9t]

I added the following in version 0.4.0:

• json(b)_array_length
• json(b)_extract_path
• json(b)_extract_path_text
• json(b)_typeof
• json(b)_strip_nulls
• jsonb_pretty

[mkurz]

Any chance you can add json_populate_recordset? Thanks!

[tmdomingues]

And json_array_elements. Much appreciated :)

[t9t]

@tmdomingues I recall that when I was looking into json_array_elements last year, my main problem was expressing the result of the function in the jOOQ API (ie. what Field type to use).

Though I can take a look at jOOQ's own DSL.unnest() to see if I can borrow that API's signature and do something similar.

@mkurz It's a similar problem as the above with json_populate_recordset, but even a level deeper (because a JSON structure is being represented by rows with columns in the result).

If you either have any suggestions how the signature of these functions would look, or an example of how you could imagine using these in your code, I'm very eager to learn about it!

Then, if I have a better view on this, I think it would be easier to figure out how to implement these.

(By the way, you're also free to contribute a pull request if you have a good idea on what this would look like ;-)

Edit: so yes, DSL.unnest() returns a Field<Table> so it can't be used in SELECT clause in jOOQ as we're used to when querying PostgreSQL directly (SELECT unnest(..) FROM table). See also: https://stackoverflow.com/questions/36572388/using-unnest-as-a-field-rather-than-a-table-in-jooq
So I fear that if I implement these functions returning Field<Table>, there will be a similar problem.

Edit2: example queries & datasets help a lot too with trying to see how to implement such functions. :)