JSON manipulation functions
in [PgSql]
|
Prev: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Next: [HACKERS] fillfactor gets set to zero for toast tables
From: Bruce Momjian on 14 May 2010 11:33 Joseph Adams wrote: > == array/object conversion == > > The json_object function converts a tuple to a JSON object. If there > are duplicate column names, there will be duplicate keys in the > resulting JSON object. > > json_object([content [AS name] [, ...]]) returns json > > Likewise, the json_array function converts a tuple to a JSON array. > Column names are ignored. > > json_array([content [AS name] [, ...]]) returns json Do you see any problems with the fact that JSON arrays can use mixed data types, e.g.: [ 1, 2, 'hi', false] -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 14 May 2010 12:17 2010/5/14 Bruce Momjian <bruce(a)momjian.us>: > Joseph Adams wrote: >> == array/object conversion == >> >> The json_object function converts a tuple to a JSON object.  If there >> are duplicate column names, there will be duplicate keys in the >> resulting JSON object. >> >> json_object([content [AS name] [, ...]]) returns json >> >> Likewise, the json_array function converts a tuple to a JSON array. >> Column names are ignored. >> >> json_array([content [AS name] [, ...]]) returns json > > Do you see any problems with the fact that JSON arrays can use mixed > data types, e.g.: > >     [ 1, 2, 'hi', false] it could not be a problem regards Pavel > > -- >  Bruce Momjian  <bruce(a)momjian.us>     http://momjian.us >  EnterpriseDB               http://enterprisedb.com > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 14 May 2010 13:15 On Thu, May 13, 2010 at 9:47 PM, Joseph Adams <joeyadams3.14159(a)gmail.com> wrote: > The following function returns the type of any JSON value. > > json_type as enum ('null', 'string', 'number', 'boolean', 'object', 'array') > json_type(json) returns json_type Seems reasonable. > Would it be a bad idea to give an enum and a function the same name > (which appears to be allowed by PostgreSQL) ? If so, json_type(json) > could be json_typeof(json) or something instead. No, I think that's a fine idea. > I thought about having predicates like IS STRING and IS NUMBER, > similar to the IS DOCUMENT predicate used for XML. However, a major > problem with that approach is that it could lead to confusion > involving IS NULL. By my understanding, the JSON datatype will just > be a specialization of TEXT (it just validates the input). Like TEXT, > a JSON string can be 'null'. 'null'::JSON is not NULL. Bear in mind > that json_to_*('null') is NULL, though. Even aside from the possible semantic confusion, I don't think that we should make any changes to our core grammar (gram.y) to support JSON. It's not really necessary and it's better not to add extra stuff to the grammar unless we really need it. > I also thought about having a series of json_is_* functions. I don't > think it's a bad idea, but I think json_type is a better solution. I agree. > == text/number/boolean conversion == > > These functions each convert a non-compound JSON value to its > respective return type. Run-time type checking is performed; a > conversion will throw an error if the input JSON is not the correct > type. If the JSON value is 'null', then the return value will be > NULL. > > json_to_text(json) returns text > json_to_number(json) returns numeric > json_to_bool(json) returns boolean Can '3' be converted to a number, or only if it's written without the quotes? > These functions convert values to JSON. Passing NULL to any of the > functions below will return 'null': > > text_to_json(text) returns json > number_to_json(numeric) returns json > bool_to_json(boolean) returns json > > There could be generic value_to_json(any), but not a > json_to_value(json) function. See > http://archives.postgresql.org/pgsql-hackers/2010-04/msg00321.php for > more details. Seems OK. > Conversion to/from number or boolean can also be achieved with > casting. Note well that '"string"'::JSON::TEXT is '"string"', not the > string's actual value. json_to_text is needed for this conversion. > For this reason, casting JSON might seem like something to recommend > against. However, IMHO, casting numbers and booleans to/from JSON is > fine and dandy; the paragraphs below give some weight to this. > > I originally considered making json_to_number and number_to_json work > with TEXT instead of NUMERIC. However, as Tom Lane pointed out in the > above link, "Forcing people to insert explicit coercions from text > isn't going to be particularly convenient to use.". Nevertheless, > NUMERIC introduces a problem. For instance, if you say: > > SELECT '-1e-38'::NUMERIC; > > This conversion knocks out the scientific notation and produces a > 41-character string. I seriously doubt that all outside applications > will handle 41-character numbers correctly. Maybe not, but I don't think it's your problem to fix it if they don't. If people want to have fine-grained control over the JSON that gets generated, they can always generate the value as text and cast it to JSON. > Perhaps there should be individual functions for specific data types, > or maybe just a handful for particular cases. There might be > json_to_int, json_to_float, and json_to_numeric. In any case, > converting to/from number types can be achieved quite easily with > casting. Personally I'd go with just json_to_numeric for starters. We can always add the others if and when it's clear that they are useful. > == array/object conversion == > > The json_object function converts a tuple to a JSON object. If there > are duplicate column names, there will be duplicate keys in the > resulting JSON object. > > json_object([content [AS name] [, ...]]) returns json Seems good. > Likewise, the json_array function converts a tuple to a JSON array. > Column names are ignored. > > json_array([content [AS name] [, ...]]) returns json I think this is pointless and should be omitted. > The json_agg function reduces a set of JSON values to a single array > containing those values. > > aggregate json_agg(json) returns json Very useful, I like that. > json_object and json_agg can be used together to convert an entire > result set to one JSON array: > > SELECT json_agg(json_object(*)) FROM tablename; Spiffy. > json_keys gets the keys of a JSON object as a set. > > json_keys(json) returns setof text I would tend to make this return text[] rather than SETOF text. > json_values gets the values of a JSON object or the iems of a JSON > array as a set. > > json_values(json) returns setof json Similarly I would make this return json[]. > Note that all JSON slicing and splicing operations retain the original > formatting of JSON content. Good. > == Miscellaneous == > > The features below would be nice, but will probably not be regarded as > required for this Google Summer of Code project to be considered > complete. > > json_cleanup accepts a superset of JSON and, if it can, cleans it up > and returns a valid JSON string. This superset of JSON supports the > following extra features: > > * Comments: > - Single-line comments with // and # > - C-style comments: /* comment */ > * Unquoted object keys: {key: "value"} > * Single quote strings: 'single quotes; "double quotes" do not need > to be escaped here' > * Single quote escape allowed: "It\'s allowed, but it's not necessary" > * Lax number format (+ sign allowed; digits may be omitted on one > side of the decimal point). > > json_cleanup(text) returns json > > Example: > > SELECT json_cleanup('{/*comment*/number: +.3}'); > -- Result is '{"number": 0.3}'::JSON This might be nice to have, or not. I don't think it's a big deal either way. > -> retrieves an item of a JSON object by key. If the object has > duplicate keys, the first key listed will be retrieved. Example: > > SELECT ('{"foo": 50, "foo": 100, "bar": "string"}'::JSON)->"foo" > -- Result is '50'::JSON > > [] retrieves a value of a JSON array/object by (one-based) index. In > other words, value[n] is equivalent to selecting the nth row of > json_values(value) (provided value is of type JSON). Examples: > > SELECT ('[1,2,3,4]'::JSON)[3] > -- Result is '3'::JSON > > SELECT ('{"a": 1, "b": 2, "c": 3, "d": 4}'::JSON)[3] > -- Result is '3'::JSON I think some kind of array deference and object deference mechanism is absolutely, positively 100% required. I don't know whether the particular syntax you've proposed here is best or whether we should pick another syntax or just use function notation, but I think we definitely need *something*. I also think we need a function called something like json_length() which returns the length of a list or the number of keys in an object. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Mike Rylander on 14 May 2010 13:47 On Fri, May 14, 2010 at 1:15 PM, Robert Haas <robertmhaas(a)gmail.com> wrote: > On Thu, May 13, 2010 at 9:47 PM, Joseph Adams > <joeyadams3.14159(a)gmail.com> wrote: [snip] >> == array/object conversion == >> >> The json_object function converts a tuple to a JSON object. If there >> are duplicate column names, there will be duplicate keys in the >> resulting JSON object. >> >> json_object([content [AS name] [, ...]]) returns json > > Seems good. > >> Likewise, the json_array function converts a tuple to a JSON array. >> Column names are ignored. >> >> json_array([content [AS name] [, ...]]) returns json > > I think this is pointless and should be omitted. > (I'm going to avoid the use of the term "object" here to reduce confusion.) I disagree with the assertion that it's pointless, and I have a specific use-case in mind for this function. I have a system that uses JSON arrays on the wire to encapsulate data, and both ends understand the positional semantics of the elements. Using JSON arrays instead of JSON objects reduces the transfer size by 40-80%, depending on how "full" the rows (or class instances) are and the data types of the elements, simply by removing the redundant object keys. This function would be extremely useful to me when creating or persisting raw class instances of these sorts. -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: miker(a)esilibrary.com | web: http://www.esilibrary.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 14 May 2010 15:09
Robert Haas <robertmhaas(a)gmail.com> writes: > On Thu, May 13, 2010 at 9:47 PM, Joseph Adams > <joeyadams3.14159(a)gmail.com> wrote: >> [] retrieves a value of a JSON array/object by (one-based) index. �In >> other words, value[n] is equivalent to selecting the nth row of >> json_values(value) (provided value is of type JSON). �Examples: >> >> SELECT ('[1,2,3,4]'::JSON)[3] >> -- Result is '3'::JSON >> >> SELECT ('{"a": 1, "b": 2, "c": 3, "d": 4}'::JSON)[3] >> -- Result is '3'::JSON > I think some kind of array deference and object deference mechanism is > absolutely, positively 100% required. I don't know whether the > particular syntax you've proposed here is best or whether we should > pick another syntax or just use function notation, but I think we > definitely need *something*. Trying to use array notation on something that isn't a SQL array type is guaranteed to be a mess. I strongly recommend that you not attempt that. Just define a function for it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |