How to calculate numbers nested in Postgresql JSON?

Question

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?

Community · Accepted Answer · 2017-05-23 11:59:02Z

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

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 16, 2015 at 17:19

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:44:30Z

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Nov 16, 2015 at 15:19

stas.yaranov

1,78711 silver badges17 bronze badges

1 Comment

n1000 Over a year ago

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.

ScienceKitten · Accepted Answer · 2019-12-19 21:28:43Z

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;

edited Dec 19, 2019 at 21:28

answered Dec 19, 2019 at 21:21

ScienceKitten

1116 bronze badges

Collectives™ on Stack Overflow

How to calculate numbers nested in Postgresql JSON?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related