CODEWITHSUNDEEP
postgresqldistance
David Zerah
David Zerah

Reputation: 3

Postgresql : Euclidian distance between array (in a json row) and an input array

Here is the structure of my database:

field data json in my database postgres for example:

{"field1": [1, 2, 3, 4], "field2": "toto"} I have another array

array_compare = [5, 6, 7, 8] Here what I want to do: return a float from data->field1 ( an array) and array_compare by using this formula :

SUM((data->field1 - array_compare)^2))^(1/2) I have tried to do many things I have not managed to do it. In fact, its a way to compute the euclidian distance between an array (in a JSON, need to be cast I guess) and an input array

Sincerly

Upvotes: 0

Views: 196

Answers (2)

Bergi
Bergi

Reputation: 664503

You're probably looking for the ROWS FROM syntax for table functions that has "the results returned in parallel columns". You'd use it with json_array_elements for the JSON array and unnest for the normal array.

SELECT id, (
  SELECT sum(sqrt(x^2 - y::numeric^2)) -- try `array_agg(x || ' ' || y)` for debugging
  FROM ROWS FROM (
    unnest(ARRAY[5, 6, 7, 8]),
    json_array_elements_text(json->'field1')
  ) as r(x, y)
) as distance
FROM example

(online demo)

Upvotes: 2

Julian
Julian

Reputation: 401

it should work using the unnest function in postgres to get a table where you can then apply the calculations, see https://www.postgresql.org/docs/9.2/functions-array.html

unnest(anyarray)    setof anyelement    expand an array to a set of rows    unnest(ARRAY[1,2])

Then you should be able to do something like

SELECT
    unnest(array[1,2,3]) AS unnest_1,
    unnest(array['a','b','c']) AS unnest_2;

would produce

 unnest_1 | unnest_2 
----------+----------
        1 | a
        2 | b
        3 | c
(3 ROWS)

(Example taken from https://www.depesz.com/2013/11/24/waiting-for-9-4-support-multi-argument-unnest-and-table-syntax-for-multiple-functions/). Then you can just do regular SQL to get the difference and sum it up.

Upvotes: 0

Related Questions