How to parse JSONB array of strings in Ruby

Question

I found many answers for parsing JSON or JSONB but it seems PostgreSQL has its own way of doing things.

I have a CSV dump from PostgreSQL that I want to parse in Ruby.

A column of the dump was made with array_agg. It is an array of strings. Unfortunately, PostgreSQL decided not to double-quote some values. It may believe they are numbers.

require 'json'

original_names = "[\"1800 Reposado 750ML\",1800Jalisco750ml,\"1800 Tequila Reposado, Jalisco, Mexico (750ml)\"]"
array = JSON.parse original_names
# Raises: 434: unexpected token at 'Jalisco750ml,"...' (JSON::ParserError)

I have tried to add the quotes myself, but it fails because of the other string that contain commas.

array = JSON.parse original_names.gsub(/,([^"|,]+),/, ',"\1",')
# Raises: 434: unexpected token at 'Jalisco", Mexico (750ml)"]' (JSON::ParserError)

I spent an hour trying workarounds and now I believe I need to implement the parser myself. Does anyone have a better way?

Values like this 1800Jalisco750ml must be wrapped within quotes — Sebastián Palma
– Sebastián Palma, Commented Nov 26, 2019 at 18:56
@SebastianPalma Exactly, that's the root issue, any way to parse and be tolerant to those values? — RaphaMex
– RaphaMex, Commented Nov 26, 2019 at 19:03
You don't want to try writing a CSV parser. CSV is a very messy data format will all sorts of gotchas. Ruby's CSV parser is pretty robust, but, again, because CSV is a mess, odds are always good something will break. Using JSON or YAML are better choices in my opinion. As for leaving your question open, don't put your solution in the question itself. Instead create an answer explaining and documenting the fix. SO will start a countdown and after it expires will allow you to select that answer as the correct one. Remove the "edit:" from your question also. (And don't use "edit" tags.) — the Tin Man
– the Tin Man, Commented Nov 26, 2019 at 20:47
I deleted my answer. You pointed out, "...It seems however that strings with , get split into several strings, which I need to avoid." I seem to have missed that effect of my answer. :-). — Cary Swoveland
– Cary Swoveland, Commented Nov 27, 2019 at 4:22

the Tin Man · Accepted Answer · 2019-11-27 00:40:06Z

1

"Array_agg in postgres selectively quotes" shows that PostgreSQL's json_agg returns proper JSON, which prevents my issue.

edited Nov 27, 2019 at 0:40

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

answered Nov 26, 2019 at 21:17

RaphaMex

2,8391 gold badge16 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

How to parse JSONB array of strings in Ruby

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related