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

Answer 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