Hello From: a [mailto:372660931@xxxxxx] 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 ---Original--- From: "Charles Clavadetscher"<clavadetscher@xxxxxxxxxxxx> Date: Wed, May 23, 2018 19:29 PM To: "'pgsql-general'"<pgsql-general@xxxxxxxxxxxxxx>;"'a'"<372660931@xxxxxx>; Subject: RE: How do I select composite array element that satisfy specific conditions.
|
