Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
select id,name, profile ->> '$."부서"', json_extract(profile, '$."부서"')
 from employees where profile ->> '$."부서"' like '개%';



JSON_Extract

컬럼에서 JSON 데이타를 추출하며 JSON Path 문법을 사용.

...

Code Block
languagesql
select id, name, json_extract(profile, '$.age') from employees where json_extract(profile, '$.age') >= 35;


JSON_Replace

JSON 컬럼에서 값을 치환해서 리턴.


다음은 age 필드의 값을 30으로 변경하는 예제

...

다음 쿼리는 전체 직원들의 나이를 1 을 더하고 부서명에 "부" 를 추가하는 예제

Code Block
languagesql
titleupdate
Update
  employees as e
  inner join (
    select id, JSON_REPLACE(profile,
        '$.age', profile ->> '$.age' + 1 ,
        '$."부서"', concat(profile ->> '$."부서"', '부')
    ) as newProfile
    from employees as p
  ) as A on e.id = A.id
set e.profile = A.newProfile;

...