Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (
  id INT,
  col1 int,
  col2 varchar,
  col3 date,
  col4 json
  );
insert into test values 
  ( 1,  1, '1',  '2025-01-01', '{"a":1,"b":2}' ::json ),
  ( 2, 10, '1',  '2025-01-01', '{"a":1,"b":2}' ::json ),
  ( 3,  1, '10', '2025-01-01', '{"a":1,"b":2}' ::json ),
  ( 4,  1, '1',  '2025-10-01', '{"a":1,"b":2}' ::json ),
  ( 5,  1, '1',  '2025-01-01', '{"a":10,"b":2}'::json),
  (10,  1, '1',  '2025-01-01', '{"a":1,"b":2}' ::json );

CREATE TABLE



INSERT 0 6


SELECT *
FROM test,
     json_each(row_to_json(test));


id
col1
col2
col3
col4
key
value


1
1
1
2025-01-01
{"a":1,"b":2}
id
1

1
1
1
2025-01-01
{"a":1,"b":2}
col1
1

1
1
1
2025-01-01
{"a":1,"b":2}
col2
"1"

1
1
1
2025-01-01
{"a":1,"b":2}
col3
"2025-01-01"

1
1
1
2025-01-01
{"a":1,"b":2}
col4
{"a":1,"b":2}

2
10
1
2025-01-01
{"a":1,"b":2}
id
2

2
10
1
2025-01-01
{"a":1,"b":2}
col1
10

2
10
1
2025-01-01
{"a":1,"b":2}
col2
"1"

2
10
1
2025-01-01
{"a":1,"b":2}
col3
"2025-01-01"

2
10
1
2025-01-01
{"a":1,"b":2}
col4
{"a":1,"b":2}

3
1
10
2025-01-01
{"a":1,"b":2}
id
3

3
1
10
2025-01-01
{"a":1,"b":2}
col1
1

3
1
10
2025-01-01
{"a":1,"b":2}
col2
"10"

3
1
10
2025-01-01
{"a":1,"b":2}
col3
"2025-01-01"

3
1
10
2025-01-01
{"a":1,"b":2}
col4
{"a":1,"b":2}

4
1
1
2025-10-01
{"a":1,"b":2}
id
4

4
1
1
2025-10-01
{"a":1,"b":2}
col1
1

4
1
1
2025-10-01
{"a":1,"b":2}
col2
"1"

4
1
1
2025-10-01
{"a":1,"b":2}
col3
"2025-10-01"

4
1
1
2025-10-01
{"a":1,"b":2}
col4
{"a":1,"b":2}

5
1
1
2025-01-01
{"a":10,"b":2}
id
5

5
1
1
2025-01-01
{"a":10,"b":2}
col1
1

5
1
1
2025-01-01
{"a":10,"b":2}
col2
"1"

5
1
1
2025-01-01
{"a":10,"b":2}
col3
"2025-01-01"

5
1
1
2025-01-01
{"a":10,"b":2}
col4
{"a":10,"b":2}

10
1
1
2025-01-01
{"a":1,"b":2}
id
10

10
1
1
2025-01-01
{"a":1,"b":2}
col1
1

10
1
1
2025-01-01
{"a":1,"b":2}
col2
"1"

10
1
1
2025-01-01
{"a":1,"b":2}
col3
"2025-01-01"

10
1
1
2025-01-01
{"a":1,"b":2}
col4
{"a":1,"b":2}



SELECT 30


SELECT id, key
FROM test,
     json_each(row_to_json(test))
WHERE json_each.value :: text LIKE '%10%'
  AND json_each.key <> 'id';


id
key


2
col1

3
col2

4
col3

5
col4



SELECT 4

id	col1	col2	col3	col4	key	value
1	1	1	2025-01-01	{"a":1,"b":2}	id	1
1	1	1	2025-01-01	{"a":1,"b":2}	col1	1
1	1	1	2025-01-01	{"a":1,"b":2}	col2	"1"
1	1	1	2025-01-01	{"a":1,"b":2}	col3	"2025-01-01"
1	1	1	2025-01-01	{"a":1,"b":2}	col4	{"a":1,"b":2}
2	10	1	2025-01-01	{"a":1,"b":2}	id	2
2	10	1	2025-01-01	{"a":1,"b":2}	col1	10
2	10	1	2025-01-01	{"a":1,"b":2}	col2	"1"
2	10	1	2025-01-01	{"a":1,"b":2}	col3	"2025-01-01"
2	10	1	2025-01-01	{"a":1,"b":2}	col4	{"a":1,"b":2}
3	1	10	2025-01-01	{"a":1,"b":2}	id	3
3	1	10	2025-01-01	{"a":1,"b":2}	col1	1
3	1	10	2025-01-01	{"a":1,"b":2}	col2	"10"
3	1	10	2025-01-01	{"a":1,"b":2}	col3	"2025-01-01"
3	1	10	2025-01-01	{"a":1,"b":2}	col4	{"a":1,"b":2}
4	1	1	2025-10-01	{"a":1,"b":2}	id	4
4	1	1	2025-10-01	{"a":1,"b":2}	col1	1
4	1	1	2025-10-01	{"a":1,"b":2}	col2	"1"
4	1	1	2025-10-01	{"a":1,"b":2}	col3	"2025-10-01"
4	1	1	2025-10-01	{"a":1,"b":2}	col4	{"a":1,"b":2}
5	1	1	2025-01-01	{"a":10,"b":2}	id	5
5	1	1	2025-01-01	{"a":10,"b":2}	col1	1
5	1	1	2025-01-01	{"a":10,"b":2}	col2	"1"
5	1	1	2025-01-01	{"a":10,"b":2}	col3	"2025-01-01"
5	1	1	2025-01-01	{"a":10,"b":2}	col4	{"a":10,"b":2}
10	1	1	2025-01-01	{"a":1,"b":2}	id	10
10	1	1	2025-01-01	{"a":1,"b":2}	col1	1
10	1	1	2025-01-01	{"a":1,"b":2}	col2	"1"
10	1	1	2025-01-01	{"a":1,"b":2}	col3	"2025-01-01"
10	1	1	2025-01-01	{"a":1,"b":2}	col4	{"a":1,"b":2}