Transform SQL JSONB array into columns by object key

Question

I have a table structured like:

CREATE TABLE artists (artist TEXT UNIQUE, facts JSONB);

INSERT INTO artists (artist, facts) 
  VALUES ('adele', '[{"type": "full_name", "value": "Adele Laurie"}, {"type": "age", "value": "25"}]');

INSERT INTO artists (artist, facts) 
  VALUES ('taylor', '[{"type": "age", "value": "25"}, {"type": "last_album", "value": "1989"}]');

There are a fixed number of fact "type"s, but not every artist will have each fact. How can I select a result with columns for each fact type and null's for missing fact names?

Desired output:

| artist |  full_name    | age  | last_album |
|--------|---------------|------|------------|
| adele  | Adele Laurie  | 25   | null       |
| taylor | null          | 25   | 1989       |

The first row has a defined object {"type": "full_name", "value": "Adele Laurie"} while the second doesn't {"age": "25"} is the second pattern right OR is there missing also a type, like {"type": "age", "value": "25"} — Jorge Campos
– Jorge Campos, Commented Nov 24, 2015 at 20:43
Oh shoot, you're right again. Sorry for the typos. Yes, the json is the same structure in both rows (with type and value keys for both). I fixed it. — Andrew C
– Andrew C, Commented Nov 24, 2015 at 20:55

Jorge Campos · Accepted Answer · 2015-11-25 01:19:12Z

1

You can do as this:

select a.artist, 
     max(case when b.value->>'type' = 'full_name' 
         then b.value->>'value' 
         else b.value->>'full_name' end) as full_name,
     max(case when b.value->>'type' = 'age' 
         then b.value->>'value' 
         else b.value->>'age' end) as age,
     max(case when b.value->>'type' = 'last_album' 
         then b.value->>'value' 
         else b.value->>'last_album' end) as last_album
from artists a, 
     json_array_elements(a.facts) b
group by a.artist
order by a.artist

See it here: http://sqlfiddle.com/#!15/e376b/2

In the fiddle I created the field as JSON since there is not available the JSONB type

If you need to add more types, just add it as a case condition like the others. I think you can figure it out from here :)

EDIT

Even with your change in the format this query should solve your problem. Just edited the fiddle. See it here: http://sqlfiddle.com/#!15/1c2b6/2

The only difference is that you don't really need the else for the case statement.

This is the query without the else statements

select a.artist, 
     max(case when b.value->>'type' = 'full_name' 
         then b.value->>'value' end) as full_name,
     max(case when b.value->>'type' = 'age' 
         then b.value->>'value' end) as age,
     max(case when b.value->>'type' = 'last_album' 
         then b.value->>'value' end) as last_album
from artists2 a, 
     json_array_elements(a.facts) b
group by a.artist
order by a.artist;

I edited the SqlFiddle link up here.

edited Nov 25, 2015 at 1:19

answered Nov 24, 2015 at 20:55

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

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

2 Comments

Andrew C Over a year ago

That works great! I'm having trouble viewing the sqlfiddle's though. Can you fix the links and edit to just answer the question as is?

Jorge Campos Over a year ago

I've edited it with the query. And the sqlfiddle links are working to me here. It sometimes stop working, lately it is unstable. Try again.

Community · Accepted Answer · 2017-05-23 12:30:18Z

0

I would go with crosstab() function. @Eriwn has really good answers with using this approach.

Example:

SELECT * FROM crosstab(
    'WITH data AS (
      SELECT artist,fact
      FROM artists a, jsonb_array_elements(a.facts) fact
    )
    SELECT artist,type,value
    FROM data,jsonb_to_record(data.fact) AS x(type text,value text)',
    'WITH data AS (
      SELECT artist,fact
      FROM artists a, jsonb_array_elements(a.facts) fact
     )
    SELECT DISTINCT type
   FROM data,jsonb_to_record(data.fact) AS x(type text,value text)'
) AS ct (artist text,age text,full_name text,last_album TEXT);

Result:

 artist | age  |  full_name   | last_album 
--------+------+--------------+------------
 adele  |      | Adele Laurie | 25
 taylor | 1989 |              | 25
(2 rows)

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Nov 24, 2015 at 21:42

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

2 Comments

Jorge Campos Over a year ago

You are using four select statements for doing something that needs only one. In a large data sets this would be an overkill. Always use K.I.S.S. methodology :)

Dzmitry Savinkou Over a year ago

Yeah, with crosstab there is no way you can reuse actual text SQL query, unless you show the one. The drawback to use 2 selects, but with adding more attributes into Json SQL should not be changed greatly @JorgeCampos

Collectives™ on Stack Overflow

Transform SQL JSONB array into columns by object key

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related