Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Using PostgreSQL for JSON Storage

info.crunchydata.com

Post reply on HN
Print view View original

21–29 of 29 posts

Re: Using PostgreSQL for JSON Storage

#21

Post by spousty »

kumarvvr wrote: post #13

Not to complain, but it seems the Web has become a huge spam bucket with useful nuggets few and far between. I have seen such articles a gazillion times till now. An article that talks about a subject, very very superficially, includes an obvious example, and concludes with a generic statement. Maybe the "users are the content creators" is not such a good idea after all.

Thanks for the feedback. I think the article was really trying to whet people's appetite for the hands on tutorial linked in the article. I wrote the blog post just to give people enough to: 1. Know they can actually store, query, and update JSON in PostgreSQL 2. Get curious and dig in deeper with the free hands on training.

Re: Using PostgreSQL for JSON Storage

#22

Post by spousty »

agentultra wrote: post #15

I use JSONB heavily in a large application in production for many years. A few things to be careful of: Unbounded Data this will have performance problems for your data set as the size of your table grows. One single value in one row that is 8MB of JSON will bring down the performance of an entire collection. which leads to Schema keep your documents structured and validate them before they touch the database. At lea…

Thanks for the this great information. In the hands on tutorial linked in the article I address some of these constraints you talk about.

Re: Using PostgreSQL for JSON Storage

#23

Post by craigkerstiens »

kumarvvr wrote: post #20
craigkerstiens wrote: post #18

Earlier quoted context omitted.

I don't disagree that this article isn't news to anyone following Postgres and having used it. Yet I also have a conversation with someone about once a week that has no idea Postgres has any form of JSON support much less whats possible with it. There are a lot more beginners out there in the world and this type of content is useful to them. A deep dive of how GIN indexing works for JSON might be a useful nugget for…

I understand your point. But my contention is that there is a lot of spam, especially in the beginner content. So much so that real useful information is nowhere to be found in search engines. I have recently started learning React and when I search for a topic, I have to wade through a myriad of articles that are basically "Get Node, Get React, CRA new, etc. etc." Nothing of actual use, but search engines pick up be…

I hear you on value of articles diving deeper. The Postgres docs are awesome, but I find them most valuable as reference docs once you already know how to do something. I find if you're trying to learn how to do a new thing they're not quite as useful.

There is definitely some value in filling the gap between a hello world of JSON and exactly how to use a specific operator. I was gonna follow up with if there are any specific things that you might find useful on the Postgres arena would be happy to start personally writing a few, but for yourself sounds like docs are good enough :)

Re: Using PostgreSQL for JSON Storage

#24

Post by kumarvvr »

craigkerstiens wrote: post #23
kumarvvr wrote: post #20

Earlier quoted context omitted.

I understand your point. But my contention is that there is a lot of spam, especially in the beginner content. So much so that real useful information is nowhere to be found in search engines. I have recently started learning React and when I search for a topic, I have to wade through a myriad of articles that are basically "Get Node, Get React, CRA new, etc. etc." Nothing of actual use, but search engines pick up be…

I hear you on value of articles diving deeper. The Postgres docs are awesome, but I find them most valuable as reference docs once you already know how to do something. I find if you're trying to learn how to do a new thing they're not quite as useful. There is definitely some value in filling the gap between a hello world of JSON and exactly how to use a specific operator. I was gonna follow up with if there are any…

Thank you for the gesture.

I love the PGSQL docs. And I usually experiment my way through the docs, as most of the time, I am usually solving a problem and will learn a bit of the underlying mechanism, quirks and tips for performance before going ahead with a method.

For those, I find those docs more than sufficient.

I found the same with Django docs too.

The worst offender is NodeJS, and to a lesser extent, React.

Edit : If only there was a method to allow users to annotate comments onto the core docs, and there was a way to upvote / downvote the comments, we could have better documentation.

I have seen primitive versions of such mechanisms in PHP docs.

Re: Using PostgreSQL for JSON Storage

#25

Post by simplify »

kumarvvr wrote: post #13

Not to complain, but it seems the Web has become a huge spam bucket with useful nuggets few and far between. I have seen such articles a gazillion times till now. An article that talks about a subject, very very superficially, includes an obvious example, and concludes with a generic statement. Maybe the "users are the content creators" is not such a good idea after all.

This type of article is just as important as the deeper ones. It's not "obvious" to everyone; most non-Postgres users don't know about its JSON features (yes, most! https://xkcd.com/1053/)

Re: Using PostgreSQL for JSON Storage

#26

Post by spousty »

spousty wrote: post #19
koolba wrote: post #8

> Let's get the users last name. SELECT json_content ##> {person, last_name} FROM mytable; > The #> or #> is the JSON path navigator with the difference being #> returns JSON and the ##> returns the JSON text value. That should be #>> (two ">"), not ##> (two "#"). The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/doc…

I guess PostgreSQL doesn't do everything for me :D Thanks for catching those and I will be updating the post soon. Did you get a chance to try the hands on exercise?

Should be fixed now - thanks!

Re: Using PostgreSQL for JSON Storage

#27

Post by wayneftw »

baq wrote: post #17
thisgoodlife wrote: post #12

Earlier quoted context omitted.

I don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.

They’d have to invent a syntax for infix function calls akin to Haskell’s backticks which other people would complain about. (I’m happy we don’t have to write queries in yaml, only half joking.)

Other popular databases have named functions for this:

https://www.sqlite.org/json1.html

https://dev.mysql.com/doc/refman/8.0/en/json-function-refere...

https://docs.microsoft.com/en-us/sql/t-sql/functions/json-fu...

(Too bad there was no standard for these.)

Re: Using PostgreSQL for JSON Storage

#28

Post by eyelidlessness »

baq wrote: post #17
thisgoodlife wrote: post #12

Earlier quoted context omitted.

I don't like those seemingly arbitrary json operators. ->> #>> ?& @>. IMHO, they'd be easier to understand with proper function names.

They’d have to invent a syntax for infix function calls akin to Haskell’s backticks which other people would complain about. (I’m happy we don’t have to write queries in yaml, only half joking.)

Why have infix anything? Functions have names and they're always the first symbol in a function call. SQL also has a convention of using `PLAIN ENGLISH WORDS` which declaratively describe the result set or operation. The weird JSONB operators are just... way out of place.

Re: Using PostgreSQL for JSON Storage

#29

Post by eyelidlessness »

wayneftw wrote: post #27
baq wrote: post #17

Earlier quoted context omitted.

They’d have to invent a syntax for infix function calls akin to Haskell’s backticks which other people would complain about. (I’m happy we don’t have to write queries in yaml, only half joking.)

Other popular databases have named functions for this: https://www.sqlite.org/json1.html https://dev.mysql.com/doc/refman/8.0/en/json-function-refere... https://docs.microsoft.com/en-us/sql/t-sql/functions/json-fu... (Too bad there was no standard for these.)

And Postgres has a ton of `JSON_` and `JSONB_` functions, so the weird operators are even more baffling.
Post reply on HN