Postgresql jsonb column to row extracted values

Question

I have the below table

BEGIN;

CREATE TABLE IF NOT EXISTS "public".appevents (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    "eventId" uuid NOT NULL,
    name text NOT NULL,
    "creationTime" timestamp without time zone NOT NULL,
    "creationTimeInMilliseconds" bigint NOT NULL,
    metadata jsonb NOT NULL,
    PRIMARY KEY(id)
);

COMMIT;

I would like to extract with a query the metadata jsonb column as a row and tried with the below query.

SELECT 
    userId
FROM 
    appevents, jsonb_to_record(appevents.metadata) as x(userId text)

Unfortunately, all the rows returned for userid have the value NULL which is not true. The only weird thing noticed is that it is converting camelcase to lowercase but doesn't seem like the issue.

Here are the 2 records I currently have in the database where userId exists.

Much simpler: SELECT metadata->>'userId' AS userid FROM appevents — Bergi
– Bergi, Commented Feb 22, 2021 at 20:59
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Feb 22, 2021 at 21:19
@Bergi I think your solution is much simpler and thank you, only problem is I have to go through all key/value pairs but it's OK. — xplat
– xplat, Commented Feb 22, 2021 at 21:29
@a_horse_with_no_name I actually didn't add that, I exported the script from the client I use, I will try to recreate the table without the quoted identifiers. I assume you're talking about the column names; — xplat
– xplat, Commented Feb 22, 2021 at 21:30

Bergi · Accepted Answer · 2021-02-23 16:30:40Z

1

The only weird thing noticed is that it is converting camelcase to lowercase but doesn't seem like the issue.

Actually that is the culprit - column names are case-insensitive by default, and userId is normalised to userid, for which the JSON doesn't contain a property. Quoting the identifier (… as x("userId" text)) should work.

However, there's a much simpler solution for accessing json object properties as text: the ->> operator. You can use

SELECT metadata->>'userId' AS userid FROM appevents

answered Feb 23, 2021 at 16:30

Bergi

670k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql jsonb column to row extracted values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related