Postgresql – find all JSON scalars in PostgreSQL 9.3

jsonpostgresqlpostgresql-9.3

We are using Postgres 9.3 and making use of the JSON column which was new to that version. Version 9.4 added a LOT of utility functions for handling JSON, as well as the new JSONB column. You can see this by comparing http://www.postgresql.org/docs/9.4/static/functions-json.html with http://www.postgresql.org/docs/9.3/static/functions-json.html

9.4 has a function, json_typeof which returns the type of a JSON object (as a string: 'string', 'number', 'array', 'object', etc.) I would like to know if there is any practical way in 9.3 of retrieving all rows which are scalars (or, equally useful for us, which are not objects).

We would like the comment column of a given table to always be a JSON object. This means that we can do queries like:

SELECT comment->>'author' as author_name
FROM our_table

If for some row the JSON in the comment column is an object, but which does not have any such key, you simply get back a NULL.

However, if there is even one scalar in the table, the query fails with the error

ERROR: cannot extract element from a scalar
SQL state: 22023

This is a pain, as we have had strings written to this column some of the time, mainly due to errors. Is there any reasonable way of either filtering these out in a query, or identifying them all so that they can be removed in one go?

Best Answer

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Related Solutions

Postgresql – How to define alias in an ARRAY_AGG expression

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array. Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.

PostgreSQL: how to query over a column with a custom range

The range type would allow best performance and simple queries.

If you have some string values like k and Pre-K mixed with numeric values, just create a grade table with a rank value (an integer value representing actual sort order):

CREATE TABLE grade(
   grade text PRIMARY KEY
  ,grade_int UNIQUE int NOT NULL
);

Your numeric grades can conveniently be assigned the same grade_value, other grades get integer values according to their rank.

You might write a simple function to convert ranges with your original string values to their integer representations on the fly ...
And vice-versa to display your grades_served column where needed.

Convert grades_served to an int4range type.
Using the overlap operator &&, your query for a range of grades can then simply be:

SELECT * FROM tbl WHERE grades_served && '[1,4]'::int4range;

Or, for a single grade, the "contains element" operator @>

SELECT * FROM tbl WHERE grades_served @> -1;

Index

If your table is not very small I suggest you create a GiST index to support that (and so does the manual).

CREATE INDEX tbl_grades_served_idx ON tbl USING gist (grades_served);

Best Answer

Related Solutions

Postgresql – How to define alias in an ARRAY_AGG expression

PostgreSQL: how to query over a column with a custom range

Index

Related Question