RE: RE: How do I select composite array element that satisfy specific conditions. - Mailing list pgsql-general

Thanks for your reply.......

Honestly I do not use java and don't really know json. All I understand is that it is a text format that allow some customization.

Java and JSON are not really related. For a simple description of JSON: http://www.json.org

However, as long as it can solve my problem, I'm happy to learn it.

now I do have a complex structure of data to store. what I'm aiming at is to:

1, orgnize the data so that it has hierarchy and structrues for people to operate.

2, all updates, insertion, will be recorded (including who, when, for what reason and which element changed from what to what).

currently I wrote a C trigger to dynamically disassemble the complex structure and compare them one by one and generate a string that printing out every change along with the update user info.

since my amount of data are not that big and the trigger is written in C, the final efficient is considerablly accepted. Now my question would be if json would be helpful on creating a relative efficient mechanism on that......

I assume that it is possible and much easier, but this would require more knowledge on the data that you want to pack in the json structure. Besides that, a basic question would be if it even necessary at all to have such a complex structure. In many cases a simpler design is more efficient.

The best thing would be to have a look at how JSON works and decide for yourself, if it helps in your case. Creating new types and aggregating them in array sounds like an overkill, but I may be mistaken.

For example your original example in JSONB could look like this:

[

  {

    "x": 1,

    "y": 2

  },

  {

    "x": 3,

    "y": 4

  }

]

In the database:

CREATE t (a JSONB);

INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');

SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';

        e

------------------

{"x": 3, "y": 4}

(1 row)

For completeness. The answer to your original question is:

SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;

  ay

-------

(3,4)

(1 row)

Regards

Charles

Hi

> -----Original Message-----
> From: a [mailto:372660931@qq.com]
> Sent: Mittwoch, 23. Mai 2018 11:43
> To: pgsql-general <pgsql-general@postgresql.org>
> Subject: How do I select composite array element that satisfy specific conditions.
>
> Hi, say if I have composite type and table
>
> create type A as(
>      x float8,
>      y float8
> );
>
> create table B(
>      Ay A[]
> );
>
> insert into B
> values(array[
>      (1,2)::A,
>      (3,4)::A]
> );
>
> How could I select the element of Ay that satisfy x=3??
>
> Thank you so much!!
>
> Shore

I did not really follow this thread, so I am not in clear, why you want to complicate your life that much.
You create a custom data type and then use it in an array in a column. A complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic very much like a school assignment.

Bye
Charles