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';

Answer 1

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"