Parsing JSON in Postgres

Question

I have the following JSON that I'd like to parse inside a postgresql function.

    {
      "people": [
        {
          "person_name": "Person#1",
          "jobs": [
            {
              "job_title": "Job#1"
            },
            {
              "job_name": "Job#2"
            }
          ]
        }
      ]
    }

I need to know how to pull out the person_name, and then loop thru the jobs and pull out the job_title. This is as far as I've been able to get.

select ('{"people":[{"person_name":"Person#1","jobs":[{"job_title":"Job#1"},
{"job_name":"Job#2"}]}]}')::json -> 'people';

Is there a difference between 'job_title' and 'job_name' or is it just a typo? — S-Man
– S-Man, Commented Aug 14, 2018 at 15:43

S-Man · Accepted Answer · 2018-08-14 15:57:36Z

https://www.db-fiddle.com/f/vcgya7WtVdvj8q5ck5TqgX/0

Assuming that job_name in your post should be job_title. I expanded your test data to:

{
    "people": [{
        "person_name": "Person#1",
        "jobs": [{
            "job_title": "Job#11"
        },
        {
            "job_title": "Job#12"
        }]
    },
    {
        "person_name": "Person#2",
        "jobs": [{
            "job_title": "Job#21"
        },
        {
            "job_title": "Job#22"
        },
        {
            "job_title": "Job#23"
        }]
    }]
}

Query:

SELECT 
    person -> 'person_name' as person_name,                            -- B
    json_array_elements(person -> 'jobs') -> 'job_title' as job_title  -- C
FROM (
    SELECT 
        json_array_elements(json_data -> 'people') as person           -- A
    FROM (
        SELECT (
            '{"people":[ '
            ||     '{"person_name":"Person#1","jobs":[{"job_title":"Job#11"}, {"job_title":"Job#12"}]}, ' 
            ||     '{"person_name":"Person#2","jobs":[{"job_title":"Job#21"}, {"job_title":"Job#22"}, {"job_title":"Job#23"}]} '
            || ']}'
        )::json as json_data
    )s
)s

A Getting person array; json_array_elements expands all array elements into one row per element

B Getting person_name from array elements

C Expanding the job array elements into one row per element and getting the job_title

Result:

person_name  job_title  
-----------  ---------  
"Person#1"   "Job#11"   
"Person#1"   "Job#12"   
"Person#2"   "Job#21"   
"Person#2"   "Job#22"   
"Person#2"   "Job#23"

Collectives™ on Stack Overflow

Parsing JSON in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related