select json b field of array postgresql of an array field in json

Question

*Postgresql version: PostgreSQL 13.5

alter table accounts
    add tags jsonb default '[]'::jsonb not null;
select DISTINCT AC.tags
     from accounts as AC where account_type = 'TEAM';

this query is returning me this result:

[
   {
      "tags":[
         "adiputra6/frontend"
      ]
   },
   {
      "tags":[
  ]

},
   {
      "tags":[
         "data extraction",
         ".net",
         "nlp"
      ]
   },
   {
      "tags":[
         "bootstrap",
         "ecommerce",
         "mysql",
         "paypal",
         "shopping cart"
      ]
   },
   {
      "tags":[
         "launcher"
      ]
   },
]

(I am using distinct cause it can have duplicate values inside the json tags, and i need a list without duplicates values)

i have tried the jsonb '->' operator:

select DISTINCT AC.tags->'tags'
 from accounts as AC where account_type = 'TEAM';

result from the previous query: [{"?column?":null}]

i would like to get a result of distinct list in a single line like:

tags: {"launcher", "bootstrap","ecommerce","mysql", "paypal","shopping cart"...}

how can i do that ?

found a solution in here but it dosen t work in my case and i can t understand why data looks the same: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc11527694d1f7bc642464f6100f49ad

Please always start by declaring your Postgres version and table definition (CREATE TABLE statement). And explain the purpose of DISTINCT in your query. It rarely makes sense to apply it to a JSON value. Finally: your query would return a set, but you display a single JSON value. Please clarify. — Erwin Brandstetter, Mar 25 '22 at 22:55
@ErwinBrandstetter thanks for your returns, i have edit the post. — tero17, Mar 25 '22 at 23:02
Still, the displayed result is a *single* JSON value, while the query would return a set of values. Is there some undeclared middleware merging the set into a JSON array? Some sample rows for the source table would be instrumental, ideally in a fiddle. Random example: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=66ef3c1777db02fdbf48ba7c822a8542 — Erwin Brandstetter, Mar 25 '22 at 23:19
@ErwinBrandstetter thank you again for your help, i have added an image to the post of field tags in account table, he is not as you have concept it in the example, pleas check. — tero17, Mar 25 '22 at 23:41
We cannot copy/paste data from an image. Ideally, provide a fiddle to work with. I could have answered your question in a minute if you had done that. — Erwin Brandstetter, Mar 25 '22 at 23:51
The fiddle contradicts your image. Please present a consistent question.. — Erwin Brandstetter, Mar 26 '22 at 02:01
Because the image has separate rows for each tags array, but the fiddle has it all in one big blob. This is exactly what @ErwinBrandstetter said to you before — Charlieface, Mar 27 '22 at 11:06

Charlieface · Answer 1 · 2022-03-28T12:17:44.120

1

Contrary to what your image shows, your fiddle has a single JSON blob, containing an array of objects, each with a tags array.

It appears you want to pull out all of these arrays, combine them into a single array, and put it into a single object with the value tags.

SELECT json_build_object('tags', jsonb_agg(DISTINCT tag.value))
FROM accounts as a
CROSS JOIN jsonb_array_elements(a.tags) AS tagsArray(value)
CROSS JOIN jsonb_array_elements_text(tagsArray.value->'tags') AS tag(value);

Result

{"tags" : ["3d printing", "adiputra6/frontend", "api", "automated testing", "bioinformatics", "c#", "c++", "callerid", "cdp", "chat", "cnam", "company", "customer data platform", "data", "data append", "dev ops", "game development", "games", "gmail", "googlechrome", "gpg", "gpg gmail chrome chromium", "haskell", "html5", "ios", "irc", "java", "maker", "metabolomics", "monitoring", "ndk", "nitrux", "notes", "open-source", "package manager", "phone", "phone data", "robotics", "roguelike", "ros", "ruby", "science", "syntax checking", "telephony", "vala", "wayland", "web game", "workflow"]}

db<>fiddle

There are a several levels here:

Shred the main array of each row of accounts into individual rows of objects.
Of each of those objects, shred the tags property also.
Aggregate distinct tag values as a JSON array.
Build a JSON object of that as the property tags.

Assuming that what you actually have is many rows, each with a single JSON array of tag strings (like the image), you can use the following simpler solution instead:

SELECT json_build_object('tags', jsonb_agg(DISTINCT tag.value))
FROM accounts as a
CROSS JOIN jsonb_array_elements(a.tags) AS tag(value);

db<>fiddle

edited Mar 28 '22 at 12:17

answered Mar 27 '22 at 11:30

Charlieface

12,780
13
35

thank you for you response, but im still not getting nothing in my real world example, im getting {"tags" : null} and i don t know why – tero17 Mar 28 '22 at 09:00
You are going to have to show your "real world example" because I used your fiddle. If your data or expected results are different then please show that in a new fiddle – Charlieface Mar 28 '22 at 09:01
acutally i think that im showing the real data in fiddl but clearly and as u sayd the problem is there how can i be sure to show the real data in fiddl ? based on the image of data what do you think is missing ? – tero17 Mar 28 '22 at 09:24
Your image shows individual rows each having a JSON array of strings, ["c#","game engine","games"] [] [] etc. Your fiddle shows something completely different: a single row of one giant JSON blob, containing an array of objects each with a tags property containing an array [{"tags":["adiputra6/frontend"]}, {"tags":[]}, ..... ] So what do you actually have? – Charlieface Mar 28 '22 at 10:13
what i really have is ["c#","game engine","games"] [] [] ... but i think that im badly putting it in the fiddl, how can i fix that in fiddl ? cause i am really not seen the difference... when i insert – tero17 Mar 28 '22 at 11:03
1

OK I have added another solution for that – Charlieface Mar 28 '22 at 12:18
thank you so much this works – tero17 Mar 28 '22 at 12:27

select json b field of array postgresql of an array field in json

1 Answers1