19.[SQL] Json 형식의 데이터를 Select 하는 방법 (MySQL, PostgreSQL)

• Using the JSON_EXTRACT() function in MySQL:
  • JSON_EXTRACT 사용하면 됩니다.
  • property_name 에는 컬럼명을 넣습니다.

SELECT JSON_EXTRACT(json_column, '$.property_name') 
FROM table_name;

This will extract the value of the "property_name" property from the JSON data in the "json_column" column.

 

• Using the JSON_VALUE() function in SQL Server
• JSON_VALUE 함수도 괜찮습니다. 
• property_name 에는 컬럼명을 넣습니다.

SELECT JSON_VALUE(json_column, '$.property_name') 
FROM table_name;

This will extract the value of the "property_name" property from the JSON data in the "json_column" column.

• PostgreSQL에서도 json_extract()를 사용할수 있습니다.
• property_name 에는 컬럼명을 넣습니다.
• Using the json_extract() function in PostgreSQL:
  • This will extract the value of the "property_name" property from the JSON data in the "json_column" column.

SELECT json_extract_path_text(json_column, 'property_name') 
FROM table_name;

• Using the json_object_keys() function in PostgreSQL:
• PostgreSQL에는 json_object_keys () 함수도 있습니다. 
• This will extract all the keys in the JSON data in the "json_column" column.
  • It's important to note that the specific syntax and functions used may vary depending on the DBMS
    you are using, and that you should consult the documentation for your specific DBMS
    for more information on working with JSON data in SQL.
• JSON_LENGTH() 함수를 사용하여 JSON 열에서 배열의 길이를 추출할 수도 있습니다.

SELECT JSON_LENGTH(json_column, '$.property1') 
FROM table_name;

• JSON_TYPE() 함수를 사용하여 열에서 JSON 데이터 유형을 추출할 수도 있습니다.

SELECT JSON_TYPE(json_column) 
FROM table_name;

 

• 파이썬과 연동해서 파싱을 한다고 하면 ? 
  • SQL의 JSON 열에서 데이터를 구문 분석한 후 Pandas와 같은 라이브러리를 사용하여 추출된 데이터로 새 데이터 프레임을 만들 수 있습니다

import pandas as pd

# Connect to the database and execute the query to extract the data from the JSON column
connection = ... # Connect to your database
cursor = connection.cursor()
cursor.execute("
	SELECT JSON_EXTRACT(json_column, '$.property_name') 
    FROM table_name
    ")

# Fetch the results of the query
results = cursor.fetchall()

# Create a new dataframe with the extracted data
df = pd.DataFrame(results, columns=['property_name'])

• MySQL 쿼리에서 여러 JSON_EXTRACT() 함수를 사용하는 경우 JSON 열에서 여러 속성 또는 값을 추출하여 결과 데이터의 개별 열에 저장할 수 있습니다.
  • 예를 들어, "table_name"이라는 테이블에 "json_column"이라는 JSON 열이 있고, 다음과 같은 JSON 데이터를 포함한다고 하면

{
    "name": "John",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "New York",
        "state": "NY"
    }
}

• 다음 쿼리를 사용하여 JSON 데이터에서 "name", "age", "street", "city" 및 "state" 속성을 추출하고 별도의 열에 저장할 수 있습니다.

SELECT JSON_EXTRACT(json_column, '$.name') as name,
       JSON_EXTRACT(json_column, '$.age') as age,
       JSON_EXTRACT(json_column, '$.address.street') as street,
       JSON_EXTRACT(json_column, '$.address.city') as city,
       JSON_EXTRACT(json_column, '$.address.state') as state
FROM table_name;

• 여기서 각 JSON_EXTRACE() 함수는 JSON 데이터에서 특정 속성을 추출하는 데 사용되며,
• 결과 값은 속성과 동일한 이름의 새 열에 저장됩니다.
• 또한 JSON_EXTRACE() 함수를 사용하여 다음과 같이 속성에 대한 경로를 지정하여 중첩된 속성에서 값을 추출할 수 있습니다:

SELECT JSON_EXTRACT(json_column, '$.property1.property2') as prop2,
       JSON_EXTRACT(json_column, '$.property1.property3') as prop3
FROM table_name;

• 이렇게 하면 "property1" 속성 내에 중첩된 "property2" 및 "property3" 속성의 값이 "json_column" 열에 있는 JSON 데이터에서 추출되어 별도의 열에 저장됩니다.
• array의 경우 JSON_EXTRACE() 함수를 특정 인덱스와 함께 사용
  하면 JSON 데이터에서 array 내의 각 속성 값을 추출할 수 있습니다.

SELECT JSON_EXTRACT(json_column, '$.property1[1]') as prop1,
       JSON_EXTRACT(json_column, '$.property2[2]') as prop2
FROM table_name;

파이썬에서는

cursor.execute("
		SELECT JSON_EXTRACT(json_column, '$.property1'),
        		JSON_EXTRACT(json_column, '$.property2') 
         FROM table_name")

results = cursor.fetchall()

df = pd.DataFrame(results, columns=['property1',
				'property2'])

이렇게 사용하면 됩니다.