CODEWITHSUNDEEP
postgresqljsonb
xplat
xplat

Reputation: 8624

Postgresql jsonb column to row extracted values

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.

Results_Rows

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

Records

Upvotes: 0

Views: 1167

Answers (1)

Bergi
Bergi

Reputation: 664513

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

Upvotes: 1

Related Questions