CODEWITHSUNDEEP
jsonpostgresql
n1000
n1000

Reputation: 5314

How to calculate numbers nested in Postgresql JSON?

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?

Upvotes: 1

Views: 130

Answers (3)

ScienceKitten
ScienceKitten

Reputation: 111

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;

Upvotes: 0

pozs
pozs

Reputation: 36244

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>.

Upvotes: 3

stas.yaranov
stas.yaranov

Reputation: 1787

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.

Upvotes: 1

Related Questions