SQL을 사용하여 JSON에서 매치 수를 세고 있습니까?

SQL을 사용하여 JSON에서 매치 수를 세고 있습니까?

다음은 예시표입니다.

id, json_txt
1, [5,18,23,50]
2, []
3, [4,5,12,18,22]
4, [18,22,50]
5, [3,12,70]

3열의 경기 개수를 합산하고 싶습니다.json_txt다른 줄에

각 행에 대한 카운트는 다음과 같습니다.

1 - 2 matches (5 and 18)
2 - 0 matches
3 - 5 matches (it's the row being searched for)
4 - 2 matches (18 and 22)
5 - 1 match (12)

이것은 제가 얻을 수 있었던 것입니다.

SELECT * FROM my_table WHERE JSON_CONTAINS(json_txt, '[4,5,12,18,22]', '$')

그러나 이는 각 행의 일치 항목 수를 세지 않고 일치 항목이 있는 행만 찾는 것입니다. :/

일단은.information_schema.tables에 대한 두 번째 인수로 인덱스 값을 결정하기 위해 행 생성에 사용됩니다.JSON_EXTRACT()기능을 같이 합니다ROW_NUMBER()DB 버전 10.2+에서 사용 가능한 window function.JSON_LENGTH()함수는 id 값이 3인 배열과 각 배열 내의 모든 요소를 비교하기 위해 최대 길이의 배열을 결정하는 데 사용됩니다.

WITH t AS
(
 SELECT t.id, JSON_EXTRACT(json_txt, CONCAT('$[', rn - 1, ']')) AS elm
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY 1) AS rn
           FROM information_schema.tables) i
          CROSS JOIN tab t
          WHERE rn <= (SELECT MAX(JSON_LENGTH(json_txt)) FROM tab)
)
SELECT CONCAT( id,' - ',COUNT(id), ' matches ', GROUP_CONCAT(elm)) AS "Result"
  FROM t
 WHERE elm IN ( SELECT elm FROM t WHERE id = 3 )
 GROUP BY id 

Demo

언급URL : https://stackoverflow.com/questions/61996875/using-sql-to-count-number-of-matches-in-json