Menu
I'm running the latest version of PostgreSQL 9.4.5-1.pgdg14.04+1, and am attempting to calculate the length of a JSONB array using the JSON_ARRAY_LENGTH function as described in the PostgreSQL 9.4 Documentation
Here is the exact query I'm attempting to run:
SELECT JSON_ARRAY_LENGTH('["hi","ho"]'::jsonb) AS length
When I run that query, I would expect to be returned a value of 2, but instead am encountering the error: ERROR: function json_array_length(jsonb) does not exist
Am I missing something very obvious in the documentation? It specifically states you may call JSON_ARRAY_LENGTH passing either a json or jsonb data-type. I'm explicitly casting to jsonb so I'm at a bit of a loss.
Has anyone else encountered this problem, or would someone point out what I'm doing wrong here?
UPDATE: I Mis-Read The Documentation
I should have been calling JSONB_ARRAY_LENGTH, not JSON_ARRAY_LENGTH. Notice the "B" after "JSON". Thanks guys.
721
PostgreSQL makes it less complicated for using arrays in a query and finding the length of a column using only the simple syntax array_length (column_name, int). The “array_length” in this syntax returns the length of an array of the first argument i.e., column_name, and “int” tells the dimension of the array measured.
Significantly faster to process. Supports indexing (which can be a significant advantage, as we'll see later). simpler schema designs (replacing entity-attribute-value (EAV) tables with JSONB columns, which can be queried, indexed, and joined, allowing for performance improvements up until 1000X!)
JSONB stands for “JSON Binary” or “JSON better” depending on whom you ask. It is a decomposed binary format to store JSON. JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
jsonb[] is not an "extra" datatype, it's simply an array of JSONB values. Similar to text[] or integer[] . You can create arrays from every type.
I have already written a couple of articles about the PostgreSQL JSON Data type and its different type of operations. JSON is one of the interesting topics or new RDBMSs, now with the new version of PostgreSQL 9.4, we can store JSON formatted data. You can visit this article, on the overview of PostgreSQL 9.4 features.
ARRAY_LENGTH() function. This function is used to return the length of the requested array dimension. Syntax: array_length(anyarray, int) Return Type: int. PostgreSQL Version: 9.3 . Example: PostgreSQL ARRAY_LENGTH() function . Code: SELECT array_length(array[1,2,3], 1); Sample Output: array_length ----- 3 (1 row)
A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension-1 elements if pretty_bool is true. array_to_json (' { {1,5}, {99,100}}'::int []) [ [1,5], [99,100]] row_to_json (record pretty_bool]) Returns the row as a JSON object.
Table 9-42. JSON Creation Functions Returns the value as JSON. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a JSON scalar value is produced.
SELECT jsonb_array_length('["question","solved"]') AS length; or
SELECT json_array_length('["question","solved"]') AS length; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
