SQL: how to return nth value from json in postgresql

Question

I am trying to SELECT and parse a javascript list in a postgres table column, it has no keys:

{coastal,transitional,contemporary,romantic,traditional,
industrial,modern,contemporary_eclectic,regency,mediterranean}

What SQL command get's the nth value?

I know you can get values by key like this:

SELECT {column_name}->>{key value}
FROM {table_name}

But I really want to just pull values by list-value order. Is there some syntax that I cannot find? Or do I need to transform this array into a different data type?

zerkms · Accepted Answer · 2016-07-22 01:10:32Z

7

The same actually works for arrays:

{column_name}->>N

where N is the integer position of an element.

References:

https://www.postgresql.org/docs/current/static/functions-json.html

answered Jul 22, 2016 at 1:10

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

3 Comments

Taylor Nelson Over a year ago

I don't know, I couldn't get that to work. I just figured out the 'ish' though: it's typical syntax for JS, just doesn't start counting at 0: e.g.: {column_name}[1] is the first value in the list

zerkms Over a year ago

@TaylorNelson it definitely is 0-base indexed.

Taylor Nelson Over a year ago

Right, I think I had a postgres array... dumb on my part, or rather, that was the real issue, I thought I had a json.

Taylor Nelson · Accepted Answer · 2016-07-28 18:02:34Z

0

Turns out I asked the wrong question--I have a postgres array, not a JSON:

I was struggling because posgres starts counting arrays at position 1, not 0--doh!

{column_name}[1] //this is the first value in the array

edited Jul 28, 2016 at 18:02

answered Jul 22, 2016 at 1:29

Taylor Nelson

211 silver badge4 bronze badges

2 Comments

zerkms Over a year ago

So, you don't have a JSON but a porstgresql array?

Taylor Nelson Over a year ago

Correct, your solution is great for my question, I asked the wrong question because I thought it was a JSON array, which we have abundantly in our schema. Thanks!

Collectives™ on Stack Overflow

SQL: how to return nth value from json in postgresql

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related