Question 89275 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL query joining a table and using json_agg() to get nested json output

So i have to tables i would like to join but get an error.

The tables look like this.
patterns table:

patternUuid urlName title
1b3d8a0f-2bc3-4c50-a00a-6cf99335dd20 card-fraud Credit Card Fraud
04bdcd7b-d61b-45cc-b9ea-c2decc77c852 return-policy-fraud Return Policy Fraud

members table:

id memberId patternsUuid
1 1 1b3d8a0f-2bc3-4c50-a00a-6cf99335dd20
2 2 1b3d8a0f-2bc3-4c50-a00a-6cf99335dd20
3 3 04bdcd7b-d61b-45cc-b9ea-c2decc77c852
4 3 04bdcd7b-d61b-45cc-b9ea-c2decc77c852

my sql that gives me an error

    SELECT "patternspatternUuid", "patterns.urlName", patterns.title, json_agg(members.memberId)
    FROM public.patterns
    JOIN members
    ON members.patternsUuid = patterns.patternUuid
    GROUP BY patterns.patternUuid

The error msg:

ERROR: column members.patternsuuid does not exist
LINE 4: ON members.patternsUuid = patterns.patternUuid
^
HINT: Perhaps you meant to reference the column "members.patternsUuid".

At the end I what to call this query and make it and endpoint and a res in json to look like this:

    "patterns": [
    {
      "patternUuid": "1b3d8a0f-2bc3-4c50-a00a-6cf99335dd20",
      "title": "Credit Card Fraud",
       "urlName": "card-fraud"
      "description": "Lorem ipsum dolor sit",
      "members": [
        {
          "memberId": 1
        },
        {
          "memberId": 2
        }
      ],
    },
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So my query ended up like this and works now

    SELECT "patternUuid", "urlName", title, json_agg(m."memberId") as members
FROM public.patterns p
JOIN members m
ON m."patternsUuid" = p."patternUuid"
GROUP BY p."patternUuid"

    Thanks for the answers as you can see, I had to use the quotes around the uppercase columns like this: members."memberId"


  2. 0

    Probably your columns are declared using quoted identifiers, hence the proper name of column in members table is patternsUuid (declared as "patternsUuid") which is case sensitive. Identifier members.patternsUuid is unquoted so it is equivalent to patternsuuid which does not match real name.

    Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search