On Mon, May 7, 2018 at 06:14:02PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/static/functions-json.html
> Description:
>
> Hi team,
>
> I had the following issue when going through your
> https://www.postgresql.org/docs/current/static/functions-json.html docs.
>
> Looking at the json_to_record example it took me quite a while that it is
> not possible to put the json_to_record function right after the the from
> clause but instead I would need to put the tables name in front, then use
> the json_to_record function. Then put the column definitions behind it and
> in the SELECT clause I need to query the columns using the alias. As you use
> a * in your examples, I assumed that json_to_record returns all values found
> in the json argument of that function.
>
> As an idea I would suggest to provide a sample json which contains key-value
> pairs as well as arrays and use this for the whole examples as someone would
> rather not query a json written by hand.
>
> Thank you very much and keep up the good work! I hope you understand and
> like my suggestion!
I think you have a good point. I was confused too and it took me a
while to get it straight. The simplest example I could create is:
CREATE TABLE test(x INT, y JSONB);
INSERT INTO test VALUES (DEFAULT,
'{"a":2,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}');
SELECT x.* FROM test, jsonb_to_record(y) AS x(a int, b text, c int[], d
text);
a | b | c | d
---+-----------+---------+---
2 | [1, 2, 3] | {1,2,3} |
While we could add this to the docs, I prefer some text that explains
how to use this, and perhaps why.
The benefits of jsonb_to_record and friends compared to typical ->
JSON[B] indexing was outlined in this thread, and I am CC'ing the author
in this thread:
https://www.postgresql.org/message-id/flat/C3E7372D-153D-4276-8DB5-0D232ECD91E4%40gmail.com
I have developed the attached doc patch which explains how to use
jsonb_to_record using a lateral reference (though the LATERAL keyword is
optional for function calls in Postgres), and a suggestion of the
performance benefits of using it. I feel text is really required to
accomplish all this, rather than an example.
Comments?
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index b851fe0..d73fdf6
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** table2-mapping
*** 11997,12009 ****
<note>
<para>
! In <function>json_populate_record</function>, <function>json_populate_recordset</function>,
! <function>json_to_record</function> and <function>json_to_recordset</function>,
! type coercion from the JSON is <quote>best effort</quote> and may not result
! in desired values for some types. JSON keys are matched to
! identical column names in the target row type. JSON fields that do not
! appear in the target row type will be omitted from the output, and
! target columns that do not match any JSON field will simply be NULL.
</para>
</note>
--- 11997,12023 ----
<note>
<para>
! While the examples for the functions
! <function>json_populate_record</function>,
! <function>json_populate_recordset</function>,
! <function>json_to_record</function> and
! <function>json_to_recordset</function> use constants, the typical use
! would be to reference a table in the <literal>FROM</literal> clause
! and use one of its <type>json</type> or <type>jsonb</type> columns
! as an argument to the function. Extracted key values can then be
! referenced in other parts of the query, like <literal>WHERE</literal>
! clauses and target lists. Extracting multiple values in this
! way can improve performance over extracting them separately with
! per-key operators.
! </para>
!
! <para>
! JSON keys are matched to identical column names in the target
! row type. JSON type coercion for these functions is <quote>best
! effort</quote> and may not result in desired values for some types.
! JSON fields that do not appear in the target row type will be
! omitted from the output, and target columns that do not match any
! JSON field will simply be NULL.
</para>
</note>
