1

I would like to do some operations with numbers stored in Postgresql 9.4 JSON format. Suppose I have this table:

CREATE TABLE t (id SERIAL, a JSON);

INSERT INTO t (a)
VALUES ('[[0.1, 2.0], [0.3, 4.0], [0.5, 6.0]]'),
       ('[[0.7, 8.0], [0.9, 10.0]]');

So I can decompose that with e.g. json_array_elements():

SELECT id, json_array_elements(a) AS sub1 FROM t;

id |   sub1
----------------
 1 | [0.1, 2.0]
 1 | [0.3, 4.0]
 1 | [0.5, 6.0]
 2 | [0.7, 8.0]
 2 | [0.9, 10.0]

Note, that the number of elements may vary. Now I would like to do some calculations with the sub-elements, like:

SELECT id,
       ((json_array_elements(a)->>0)::FLOAT) *
       ((json_array_elements(a)->>1)::FLOAT) as result
FROM t

However I get the error: ERROR: functions and operators can take at most one set argument. Any hints?

n1000
  • 5,058
  • 10
  • 37
  • 65

3 Answers3

3

Use an (implicit) LATERAL join instead:

SELECT id, (e->>0)::float * (e->>1)::float as result
FROM   t, json_array_elements(a) e

Set-returning functions in the SELECT works very special in PostgreSQL:

The exact reason of your error is that, you tried to multiply two sets (which is not allowed). PostgreSQL can calculate <element> * <element> (will be single value), <set> * <element> (or vice versa; will be set-valued), but not <set> * <set>.

Community
  • 1
  • 1
pozs
  • 34,608
  • 5
  • 57
  • 63
1

That's because json_array_elements() returns setof json.

You should better access elements by path, like this:

SELECT
        id,
        (a#>>'{0,0}')::float * (a#>>'{0,1}')::float as result
    FROM processing.t;

And about error is here.

If this is not an option, you can use a subquery:

select
        id,
        (a->>0)::float * (a->>1)::float as result
    from (
        SELECT
                id,
                json_array_elements(a) as a
            FROM processing.t
    ) t;

In this case you are able to write expressions the way you wanted to.

Community
  • 1
  • 1
stas.yaranov
  • 1,797
  • 10
  • 17
  • Thanks! That's a nice answer. But actually I do not know how many items will be nested within the first element. So I'd need a more flexible solution. I will try to update my question. – n1000 Nov 16 '15 at 15:30
0

If version of PostgreSQL is equal or more than 10 this code from current question, do not cause error. I made fiddle with this.

CREATE TABLE t (id SERIAL, a JSON);

INSERT INTO t (a)
VALUES ('[[0.1, 2.0], [0.3, 4.0], [0.5, 6.0]]'),
       ('[[0.7, 8.0], [0.9, 10.0]]');

SELECT id,
       ((json_array_elements(a)->>0)::FLOAT) *
       ((json_array_elements(a)->>1)::FLOAT) as result
FROM t;
ScienceKitten
  • 101
  • 6