Ancestors

Toot

Written by Henrik Jernevad on 2024-10-08 at 11:41

[#]TIL: You can use the ->> operator in PostgreSQL to select fields inside a JSON objects stored in a text column.

For example, assuming that events.data is a text column containing a JSON object:

SELECT
	events.timestamp,
	events.data::json->>'field1' AS field1
	events.data::json->>'field2' AS field2
FROM events

[#]sql #postgresql #json

=> More informations about this toot | More toots from henrikjernevad@mastodon.social

Descendants

Written by Steve Purcell on 2024-10-08 at 13:29

@henrikjernevad Yes, and also worth noting that it's even better to make those columns JSONB instead of TEXT, so that a) the JSON is guaranteed valid and b) operations like ->> are much faster. Plus you can then add specialised indexes to those columns if necessary.

=> More informations about this toot | More toots from sanityinc@hachyderm.io

Written by Henrik Jernevad on 2024-10-08 at 14:28

@sanityinc Good point! 👍 In my case the schema was out of my control, so I was happy to see that I could do the conversion "on the fly" and query JSON fields anyway. 😊

=> More informations about this toot | More toots from henrikjernevad@mastodon.social

Written by Steve Purcell on 2024-10-08 at 17:02

@henrikjernevad yeah, hands tied in that case!

=> More informations about this toot | More toots from sanityinc@hachyderm.io