Handling null or missing attributes in JSON using PostgreSQL

Question

I'm learning how to handle JSON in PostgreSQL.

I have a table with some columns. One column is a JSON field. The data in that column has at least these three variations:

Case 1: {"createDate": 1448067864151, "name": "world"}
Case 2: {"createDate": "", "name": "hello"}
Case 3: {"name": "sky"}

Later on, I want to select the createDate.

TO_TIMESTAMP((attributes->>'createDate')::bigint * 0.001)

That works fine for Case 1 when the data is present and it is convertible to a bigint. But what about when it isn't? How do I handle this?

I read this article. It explains that we can add check constraints to perform some rudimentary validation. Alternatively, I could do a schema validation before the data is inserts (on the client side). There are pros and cons with both ideas.

Using a Check Constraint

CONSTRAINT validate_createDate CHECK ((attributes->>'createDate')::bigint >= 1)

This forces a non-nullable field (Case 3 fails). But I want the attribute to be optional. Furthermore, if the attribute doesn't convert to a bigint because it is blank (Case 2), this errors out.

Using JSON schema validation on the client side before insert

This works, in part, because the schema validation makes sure that what data comes in conforms to the schema. In my case, I can control which clients access this table, so this is OK. But it doesn't matter for the SQL later on since my validator will let pass all three cases.

Dzmitry Savinkou · Accepted Answer · 2016-01-13 15:01:30Z

6

Basically, you need to check if createDate attribute is empty:

WITH data(attributes) AS ( VALUES
  ('{"createDate": 1448067864151, "name": "world"}'::JSON),
  ('{"createDate": "", "name": "hello"}'::JSON),
  ('{"name": "sky"}'::JSON)
)
SELECT to_timestamp((attributes->>'createDate')::bigint * 0.001) FROM data
WHERE
  (attributes->>'createDate') IS NOT NULL
AND
  (attributes->>'createDate') != '';

Output:

        to_timestamp        
----------------------------
 2015-11-20 17:04:24.151-08
(1 row)

answered Jan 13, 2016 at 15:01

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:24:00Z

3

Building on Dmitry's answer, you can also check the json type with the json_typeof function. Note the json operator: -> to get json instead of the ->> operator which always casts the value to string.

By doing the check in the SELECT with a CASE conditional instead of in the WHERE clause, we also keep the rows not having a createdDate. Depending on your usecase, this might be better.

WITH data(attributes) AS ( VALUES
  ('{"createDate": 1448067864151, "name": "world"}'::JSON),
  ('{"createDate": "", "name": "hello"}'::JSON),
  ('{"name": "sky"}'::JSON)
)
SELECT
    CASE WHEN (json_typeof(attributes->'createDate') = 'number')
         THEN to_timestamp((attributes->>'createDate')::bigint * 0.001)
    END AS created_date
FROM data
;

Output:

created_date
----------------------------
"2015-11-21 02:04:24.151+01"
""
""
(3 rows)

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered May 24, 2016 at 10:42

Geir Bostad

9267 silver badges19 bronze badges

1 Comment

Hammad Over a year ago

this selects 3 rows but doesnt the OP want only 1 row on which that condition of existence holds?

Collectives™ on Stack Overflow

Handling null or missing attributes in JSON using PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related