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';

enter image description here

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

• Erwin Brandstetter: 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.
• tero17: @ErwinBrandstetter thanks for your returns, i have edit the post.
• Erwin Brandstetter: 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: dbfiddle.uk/…
• tero17: @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.
• Erwin Brandstetter: Please post sample data as text, never as image.

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

• tero17: 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
• Charlieface: 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
• tero17: 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 ?
• Charlieface: 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: OK I have added another solution for that