Repository: duckdb/duckdb (language: python)

Issue:
Extracting a key that is null in json returns a non-null value
### What happens?


When extracting from json, if a key in the json has a null value, it's returned as a non-null object in SQL.  This is not what I would expect at all, and breaks things like COALESCE.  

<img width="408" alt="image" src="https://user-images.githubusercontent.com/6359/226137555-5596c017-0511-4f34-a8f9-a1c9239f9de4.png">
<img width="460" alt="image" src="https://user-images.githubusercontent.com/6359/226137628-03b875e9-7075-44ee-8783-c55615efcfee.png">



### To Reproduce

Pretty easy to see what I mean by running this snippet:

```
select json_extract('{ "foo": null }', '$.foo') is null;
```


### OS:

macOS / m1

### DuckDB Version:

v0.7.1 b00b93f0b1

### DuckDB Client:

CLI & typescript

### Full Name:

Ken Miller

### Affiliation:

Watershed

### Have you tried this on the latest `master` branch?

- [x] I agree

### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

- [X] I agree
JSON_TYPE function produces wrong result if path is a column expression
### What happens?

The `JSON_TYPE` function produces wrong results depending on how I'm passing the path argument

### To Reproduce

Run this query:

```sql
SELECT 
  json_type (json '{"a":1,"b":null}', p),
  json_type (json '{"a":1,"b":null}', 'b')
FROM (VALUES ('b')) AS t (p);
```

This produces:

```
|json_type(CAST('{"a":1,"b":null}' AS "json"), p)|json_type(CAST('{"a":1,"b":null}' AS "json"), 'b')|
|------------------------------------------------|--------------------------------------------------|
|                                                |NULL                                              |
```

The first result is wrong (SQL `NULL`). It should be just like the second result `'NULL'`

### OS:

Microsoft Windows [Version 10.0.22631.3880]

### DuckDB Version:

1.0.0

### DuckDB Client:

JDBC

### Full Name:

Lukas Eder

### Affiliation:

Data Geekery

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Interestingly, converting to a struct and then extracting does the trick.  So we do have a workaround, but this doesn't seem like the right behavior to me.


![image](https://user-images.githubusercontent.com/6359/226141368-ac700ea3-8d6f-4d3d-b84d-8d3a3d6c30ff.png)


It also works correctly if the key is just missing.
`IS NULL` is likely not implemented correctly for the JSON type
We noticed it because the output didn't behave correctly in COALESCE()
I'm not that familiar with the JSON type, but internally it's just a string, so "null" isn't recognized as being NULL.
Which makes sense, but we probably need to add some special handling to mark these entries as being NULL
Bump -- any news on this one?
This is because `null` is valid `JSON`, and therefore is not DuckDB's `NULL`. This should probably be changed to go to our `NULL` once extracted.
Closed via #7478
Fantastic, thank you!



Fix patch:
diff --git a/extension/json/CMakeLists.txt b/extension/json/CMakeLists.txt
index aefc1c3c8a92..c39a36162c8c 100644
--- a/extension/json/CMakeLists.txt
+++ b/extension/json/CMakeLists.txt
@@ -18,6 +18,7 @@ set(JSON_EXTENSION_FILES
     json_functions/copy_json.cpp
     json_functions/json_array_length.cpp
     json_functions/json_contains.cpp
+    json_functions/json_exists.cpp
     json_functions/json_extract.cpp
     json_functions/json_keys.cpp
     json_functions/json_merge_patch.cpp
@@ -27,6 +28,7 @@ set(JSON_EXTENSION_FILES
     json_functions/json_create.cpp
     json_functions/json_type.cpp
     json_functions/json_valid.cpp
+    json_functions/json_value.cpp
     json_functions/json_serialize_sql.cpp
     json_functions/json_serialize_plan.cpp
     json_functions/read_json.cpp
diff --git a/extension/json/include/json_executors.hpp b/extension/json/include/json_executors.hpp
index 78da4526842f..0eeff5e40bc6 100644
--- a/extension/json/include/json_executors.hpp
+++ b/extension/json/include/json_executors.hpp
@@ -13,26 +13,28 @@
 
 namespace duckdb {
 
+template <class T>
+using json_function_t = std::function<T(yyjson_val *, yyjson_alc *, Vector &, ValidityMask &, idx_t)>;
+
 struct JSONExecutors {
 public:
 	//! Single-argument JSON read function, i.e. json_type('[1, 2, 3]')
 	template <class T>
-	static void UnaryExecute(DataChunk &args, ExpressionState &state, Vector &result,
-	                         std::function<T(yyjson_val *, yyjson_alc *, Vector &)> fun) {
+	static void UnaryExecute(DataChunk &args, ExpressionState &state, Vector &result, const json_function_t<T> fun) {
 		auto &lstate = JSONFunctionLocalState::ResetAndGet(state);
 		auto alc = lstate.json_allocator.GetYYAlc();
 
 		auto &inputs = args.data[0];
-		UnaryExecutor::Execute<string_t, T>(inputs, result, args.size(), [&](string_t input) {
-			auto doc = JSONCommon::ReadDocument(input, JSONCommon::READ_FLAG, alc);
-			return fun(doc->root, alc, result);
-		});
+		UnaryExecutor::ExecuteWithNulls<string_t, T>(
+		    inputs, result, args.size(), [&](string_t input, ValidityMask &mask, idx_t idx) {
+			    auto doc = JSONCommon::ReadDocument(input, JSONCommon::READ_FLAG, alc);
+			    return fun(doc->root, alc, result, mask, idx);
+		    });
 	}
 
 	//! Two-argument JSON read function (with path query), i.e. json_type('[1, 2, 3]', '$[0]')
-	template <class T, bool NULL_IF_NULL = true>
-	static void BinaryExecute(DataChunk &args, ExpressionState &state, Vector &result,
-	                          std::function<T(yyjson_val *, yyjson_alc *, Vector &)> fun) {
+	template <class T, bool SET_NULL_IF_NOT_FOUND = true>
+	static void BinaryExecute(DataChunk &args, ExpressionState &state, Vector &result, const json_function_t<T> fun) {
 		auto &func_expr = state.expr.Cast<BoundFunctionExpression>();
 		const auto &info = func_expr.bind_info->Cast<JSONReadFunctionData>();
 		auto &lstate = JSONFunctionLocalState::ResetAndGet(state);
@@ -48,11 +50,11 @@ struct JSONExecutors {
 					    auto doc =
 					        JSONCommon::ReadDocument(input, JSONCommon::READ_FLAG, lstate.json_allocator.GetYYAlc());
 					    auto val = JSONCommon::GetUnsafe(doc->root, ptr, len);
-					    if (!val || (NULL_IF_NULL && unsafe_yyjson_is_null(val))) {
+					    if (SET_NULL_IF_NOT_FOUND && !val) {
 						    mask.SetInvalid(idx);
 						    return T {};
 					    } else {
-						    return fun(val, alc, result);
+						    return fun(val, alc, result, mask, idx);
 					    }
 				    });
 			} else {
@@ -76,11 +78,7 @@ struct JSONExecutors {
 					for (idx_t i = 0; i < vals.size(); i++) {
 						auto &val = vals[i];
 						D_ASSERT(val != nullptr); // Wildcard extract shouldn't give back nullptrs
-						if (NULL_IF_NULL && unsafe_yyjson_is_null(val)) {
-							child_validity.SetInvalid(current_size + i);
-						} else {
-							child_vals[current_size + i] = fun(val, alc, result);
-						}
+						child_vals[current_size + i] = fun(val, alc, result, child_validity, current_size + i);
 					}
 
 					ListVector::SetListSize(result, new_size);
@@ -95,11 +93,11 @@ struct JSONExecutors {
 			    inputs, paths, result, args.size(), [&](string_t input, string_t path, ValidityMask &mask, idx_t idx) {
 				    auto doc = JSONCommon::ReadDocument(input, JSONCommon::READ_FLAG, lstate.json_allocator.GetYYAlc());
 				    auto val = JSONCommon::Get(doc->root, path);
-				    if (!val || unsafe_yyjson_is_null(val)) {
+				    if (SET_NULL_IF_NOT_FOUND && !val) {
 					    mask.SetInvalid(idx);
 					    return T {};
 				    } else {
-					    return fun(val, alc, result);
+					    return fun(val, alc, result, mask, idx);
 				    }
 			    });
 		}
@@ -109,9 +107,8 @@ struct JSONExecutors {
 	}
 
 	//! JSON read function with list of path queries, i.e. json_type('[1, 2, 3]', ['$[0]', '$[1]'])
-	template <class T, bool NULL_IF_NULL = true>
-	static void ExecuteMany(DataChunk &args, ExpressionState &state, Vector &result,
-	                        std::function<T(yyjson_val *, yyjson_alc *, Vector &)> fun) {
+	template <class T, bool SET_NULL_IF_NOT_FOUND = true>
+	static void ExecuteMany(DataChunk &args, ExpressionState &state, Vector &result, const json_function_t<T> fun) {
 		auto &func_expr = state.expr.Cast<BoundFunctionExpression>();
 		const auto &info = func_expr.bind_info->Cast<JSONReadManyFunctionData>();
 		auto &lstate = JSONFunctionLocalState::ResetAndGet(state);
@@ -148,10 +145,10 @@ struct JSONExecutors {
 			for (idx_t path_i = 0; path_i < num_paths; path_i++) {
 				auto child_idx = offset + path_i;
 				val = JSONCommon::GetUnsafe(doc->root, info.ptrs[path_i], info.lens[path_i]);
-				if (!val || (NULL_IF_NULL && unsafe_yyjson_is_null(val))) {
+				if (SET_NULL_IF_NOT_FOUND && !val) {
 					child_validity.SetInvalid(child_idx);
 				} else {
-					child_data[child_idx] = fun(val, alc, child);
+					child_data[child_idx] = fun(val, alc, child, child_validity, child_idx);
 				}
 			}
 
diff --git a/extension/json/include/json_functions.hpp b/extension/json/include/json_functions.hpp
index d8e2103cee50..cd19f3735478 100644
--- a/extension/json/include/json_functions.hpp
+++ b/extension/json/include/json_functions.hpp
@@ -96,9 +96,11 @@ class JSONFunctions {
 
 	static ScalarFunctionSet GetArrayLengthFunction();
 	static ScalarFunctionSet GetContainsFunction();
+	static ScalarFunctionSet GetExistsFunction();
 	static ScalarFunctionSet GetKeysFunction();
 	static ScalarFunctionSet GetTypeFunction();
 	static ScalarFunctionSet GetValidFunction();
+	static ScalarFunctionSet GetValueFunction();
 	static ScalarFunctionSet GetSerializeSqlFunction();
 	static ScalarFunctionSet GetDeserializeSqlFunction();
 	static ScalarFunctionSet GetSerializePlanFunction();
diff --git a/extension/json/json_config.py b/extension/json/json_config.py
index 8f888c6bb6d6..1fe19b1a3010 100644
--- a/extension/json/json_config.py
+++ b/extension/json/json_config.py
@@ -15,6 +15,7 @@
         'extension/json/json_functions/copy_json.cpp',
         'extension/json/json_functions/json_array_length.cpp',
         'extension/json/json_functions/json_contains.cpp',
+        'extension/json/json_functions/json_exists.cpp',
         'extension/json/json_functions/json_extract.cpp',
         'extension/json/json_functions/json_keys.cpp',
         'extension/json/json_functions/json_merge_patch.cpp',
@@ -24,6 +25,7 @@
         'extension/json/json_functions/json_create.cpp',
         'extension/json/json_functions/json_type.cpp',
         'extension/json/json_functions/json_valid.cpp',
+        'extension/json/json_functions/json_value.cpp',
         'extension/json/json_functions/read_json_objects.cpp',
         'extension/json/json_functions/read_json.cpp',
         'extension/json/json_functions/json_serialize_plan.cpp',
diff --git a/extension/json/json_functions.cpp b/extension/json/json_functions.cpp
index 76d6360417ad..0ad6837690b6 100644
--- a/extension/json/json_functions.cpp
+++ b/extension/json/json_functions.cpp
@@ -160,9 +160,11 @@ vector<ScalarFunctionSet> JSONFunctions::GetScalarFunctions() {
 	// Other
 	functions.push_back(GetArrayLengthFunction());
 	functions.push_back(GetContainsFunction());
+	functions.push_back(GetExistsFunction());
 	functions.push_back(GetKeysFunction());
 	functions.push_back(GetTypeFunction());
 	functions.push_back(GetValidFunction());
+	functions.push_back(GetValueFunction());
 	functions.push_back(GetSerializePlanFunction());
 	functions.push_back(GetSerializeSqlFunction());
 	functions.push_back(GetDeserializeSqlFunction());
diff --git a/extension/json/json_functions/CMakeLists.txt b/extension/json/json_functions/CMakeLists.txt
index 1c0dd1ea63fa..bf96c8391b51 100644
--- a/extension/json/json_functions/CMakeLists.txt
+++ b/extension/json/json_functions/CMakeLists.txt
@@ -4,6 +4,7 @@ add_library_unity(
   copy_json.cpp
   json_array_length.cpp
   json_contains.cpp
+  json_exists.cpp
   json_extract.cpp
   json_keys.cpp
   json_merge_patch.cpp
@@ -13,6 +14,7 @@ add_library_unity(
   json_create.cpp
   json_type.cpp
   json_valid.cpp
+  json_value.cpp
   json_serialize_plan.cpp
   json_serialize_sql.cpp
   read_json.cpp
diff --git a/extension/json/json_functions/json_array_length.cpp b/extension/json/json_functions/json_array_length.cpp
index fc33f37143ec..c487239b820d 100644
--- a/extension/json/json_functions/json_array_length.cpp
+++ b/extension/json/json_functions/json_array_length.cpp
@@ -2,7 +2,7 @@
 
 namespace duckdb {
 
-static inline uint64_t GetArrayLength(yyjson_val *val, yyjson_alc *alc, Vector &result) {
+static inline uint64_t GetArrayLength(yyjson_val *val, yyjson_alc *, Vector &, ValidityMask &, idx_t) {
 	return yyjson_arr_size(val);
 }
 
diff --git a/extension/json/json_functions/json_exists.cpp b/extension/json/json_functions/json_exists.cpp
new file mode 100644
index 000000000000..f9d3548b7070
--- /dev/null
+++ b/extension/json/json_functions/json_exists.cpp
@@ -0,0 +1,32 @@
+#include "json_executors.hpp"
+
+namespace duckdb {
+
+static inline bool JSONExists(yyjson_val *val, yyjson_alc *, Vector &, ValidityMask &, idx_t) {
+	return val;
+}
+
+static void BinaryExistsFunction(DataChunk &args, ExpressionState &state, Vector &result) {
+	JSONExecutors::BinaryExecute<bool, false>(args, state, result, JSONExists);
+}
+
+static void ManyExistsFunction(DataChunk &args, ExpressionState &state, Vector &result) {
+	JSONExecutors::ExecuteMany<bool, false>(args, state, result, JSONExists);
+}
+
+static void GetExistsFunctionsInternal(ScalarFunctionSet &set, const LogicalType &input_type) {
+	set.AddFunction(ScalarFunction({input_type, LogicalType::VARCHAR}, LogicalType::BOOLEAN, BinaryExistsFunction,
+	                               JSONReadFunctionData::Bind, nullptr, nullptr, JSONFunctionLocalState::Init));
+	set.AddFunction(ScalarFunction({input_type, LogicalType::LIST(LogicalType::VARCHAR)},
+	                               LogicalType::LIST(LogicalType::BOOLEAN), ManyExistsFunction,
+	                               JSONReadManyFunctionData::Bind, nullptr, nullptr, JSONFunctionLocalState::Init));
+}
+
+ScalarFunctionSet JSONFunctions::GetExistsFunction() {
+	ScalarFunctionSet set("json_exists");
+	GetExistsFunctionsInternal(set, LogicalType::VARCHAR);
+	GetExistsFunctionsInternal(set, LogicalType::JSON());
+	return set;
+}
+
+} // namespace duckdb
diff --git a/extension/json/json_functions/json_extract.cpp b/extension/json/json_functions/json_extract.cpp
index 59daa49ccc8e..2fc32612088f 100644
--- a/extension/json/json_functions/json_extract.cpp
+++ b/extension/json/json_functions/json_extract.cpp
@@ -2,11 +2,11 @@
 
 namespace duckdb {
 
-static inline string_t ExtractFromVal(yyjson_val *val, yyjson_alc *alc, Vector &) {
+static inline string_t ExtractFromVal(yyjson_val *val, yyjson_alc *alc, Vector &, ValidityMask &, idx_t) {
 	return JSONCommon::WriteVal<yyjson_val>(val, alc);
 }
 
-static inline string_t ExtractStringFromVal(yyjson_val *val, yyjson_alc *alc, Vector &) {
+static inline string_t ExtractStringFromVal(yyjson_val *val, yyjson_alc *alc, Vector &, ValidityMask &, idx_t) {
 	return yyjson_is_str(val) ? string_t(unsafe_yyjson_get_str(val), unsafe_yyjson_get_len(val))
 	                          : JSONCommon::WriteVal<yyjson_val>(val, alc);
 }
diff --git a/extension/json/json_functions/json_keys.cpp b/extension/json/json_functions/json_keys.cpp
index eb9918839e9c..0b672c0867d9 100644
--- a/extension/json/json_functions/json_keys.cpp
+++ b/extension/json/json_functions/json_keys.cpp
@@ -2,7 +2,7 @@
 
 namespace duckdb {
 
-static inline list_entry_t GetJSONKeys(yyjson_val *val, yyjson_alc *alc, Vector &result) {
+static inline list_entry_t GetJSONKeys(yyjson_val *val, yyjson_alc *, Vector &result, ValidityMask &, idx_t) {
 	auto num_keys = yyjson_obj_size(val);
 	auto current_size = ListVector::GetListSize(result);
 	auto new_size = current_size + num_keys;
diff --git a/extension/json/json_functions/json_pretty.cpp b/extension/json/json_functions/json_pretty.cpp
index 16240917acb0..1fb96081d6b6 100644
--- a/extension/json/json_functions/json_pretty.cpp
+++ b/extension/json/json_functions/json_pretty.cpp
@@ -3,7 +3,7 @@
 namespace duckdb {
 
 //! Pretty Print a given JSON Document
-string_t PrettyPrint(yyjson_val *val, yyjson_alc *alc, Vector &result) {
+string_t PrettyPrint(yyjson_val *val, yyjson_alc *alc, Vector &, ValidityMask &, idx_t) {
 	D_ASSERT(alc);
 	idx_t len;
 	auto data =
diff --git a/extension/json/json_functions/json_structure.cpp b/extension/json/json_functions/json_structure.cpp
index 9f4593e5ce27..0480057218d3 100644
--- a/extension/json/json_functions/json_structure.cpp
+++ b/extension/json/json_functions/json_structure.cpp
@@ -499,7 +499,7 @@ static yyjson_mut_val *ConvertStructure(const JSONStructureNode &node, yyjson_mu
 	}
 }
 
-static string_t JSONStructureFunction(yyjson_val *val, yyjson_alc *alc, Vector &) {
+static string_t JSONStructureFunction(yyjson_val *val, yyjson_alc *alc, Vector &, ValidityMask &, idx_t) {
 	return JSONCommon::WriteVal<yyjson_mut_val>(
 	    ConvertStructure(ExtractStructureInternal(val, true), yyjson_mut_doc_new(alc)), alc);
 }
diff --git a/extension/json/json_functions/json_type.cpp b/extension/json/json_functions/json_type.cpp
index 8f3fb3ad9722..47aec2d663df 100644
--- a/extension/json/json_functions/json_type.cpp
+++ b/extension/json/json_functions/json_type.cpp
@@ -2,7 +2,7 @@
 
 namespace duckdb {
 
-static inline string_t GetType(yyjson_val *val, yyjson_alc *alc, Vector &result) {
+static inline string_t GetType(yyjson_val *val, yyjson_alc *, Vector &, ValidityMask &mask, idx_t idx) {
 	return JSONCommon::ValTypeToStringT(val);
 }
 
@@ -11,11 +11,11 @@ static void UnaryTypeFunction(DataChunk &args, ExpressionState &state, Vector &r
 }
 
 static void BinaryTypeFunction(DataChunk &args, ExpressionState &state, Vector &result) {
-	JSONExecutors::BinaryExecute<string_t, false>(args, state, result, GetType);
+	JSONExecutors::BinaryExecute<string_t>(args, state, result, GetType);
 }
 
 static void ManyTypeFunction(DataChunk &args, ExpressionState &state, Vector &result) {
-	JSONExecutors::ExecuteMany<string_t, false>(args, state, result, GetType);
+	JSONExecutors::ExecuteMany<string_t>(args, state, result, GetType);
 }
 
 static void GetTypeFunctionsInternal(ScalarFunctionSet &set, const LogicalType &input_type) {
diff --git a/extension/json/json_functions/json_value.cpp b/extension/json/json_functions/json_value.cpp
new file mode 100644
index 000000000000..06afbd94594e
--- /dev/null
+++ b/extension/json/json_functions/json_value.cpp
@@ -0,0 +1,42 @@
+#include "json_executors.hpp"
+
+namespace duckdb {
+
+static inline string_t ValueFromVal(yyjson_val *val, yyjson_alc *alc, Vector &, ValidityMask &mask, idx_t idx) {
+	switch (yyjson_get_tag(val)) {
+	case YYJSON_TYPE_ARR | YYJSON_SUBTYPE_NONE:
+	case YYJSON_TYPE_OBJ | YYJSON_SUBTYPE_NONE:
+		mask.SetInvalid(idx);
+		return string_t {};
+	default:
+		return JSONCommon::WriteVal<yyjson_val>(val, alc);
+	}
+}
+
+static void ValueFunction(DataChunk &args, ExpressionState &state, Vector &result) {
+	JSONExecutors::BinaryExecute<string_t>(args, state, result, ValueFromVal);
+}
+
+static void ValueManyFunction(DataChunk &args, ExpressionState &state, Vector &result) {
+	JSONExecutors::ExecuteMany<string_t>(args, state, result, ValueFromVal);
+}
+
+static void GetValueFunctionsInternal(ScalarFunctionSet &set, const LogicalType &input_type) {
+	set.AddFunction(ScalarFunction({input_type, LogicalType::BIGINT}, LogicalType::JSON(), ValueFunction,
+	                               JSONReadFunctionData::Bind, nullptr, nullptr, JSONFunctionLocalState::Init));
+	set.AddFunction(ScalarFunction({input_type, LogicalType::VARCHAR}, LogicalType::JSON(), ValueFunction,
+	                               JSONReadFunctionData::Bind, nullptr, nullptr, JSONFunctionLocalState::Init));
+	set.AddFunction(ScalarFunction({input_type, LogicalType::LIST(LogicalType::VARCHAR)},
+	                               LogicalType::LIST(LogicalType::JSON()), ValueManyFunction,
+	                               JSONReadManyFunctionData::Bind, nullptr, nullptr, JSONFunctionLocalState::Init));
+}
+
+ScalarFunctionSet JSONFunctions::GetValueFunction() {
+	// The value function is just like the extract function but returns NULL if the JSON is not a scalar value
+	ScalarFunctionSet set("json_value");
+	GetValueFunctionsInternal(set, LogicalType::VARCHAR);
+	GetValueFunctionsInternal(set, LogicalType::JSON());
+	return set;
+}
+
+} // namespace duckdb
diff --git a/src/include/duckdb/main/extension_entries.hpp b/src/include/duckdb/main/extension_entries.hpp
index c453cd6b1a7b..5988d96ce2ae 100644
--- a/src/include/duckdb/main/extension_entries.hpp
+++ b/src/include/duckdb/main/extension_entries.hpp
@@ -200,6 +200,7 @@ static constexpr ExtensionFunctionEntry EXTENSION_FUNCTIONS[] = {
     {"json_deserialize_sql", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_execute_serialized_sql", "json", CatalogType::PRAGMA_FUNCTION_ENTRY},
     {"json_execute_serialized_sql", "json", CatalogType::TABLE_FUNCTION_ENTRY},
+    {"json_exists", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_extract", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_extract_path", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_extract_path_text", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
@@ -219,6 +220,7 @@ static constexpr ExtensionFunctionEntry EXTENSION_FUNCTIONS[] = {
     {"json_transform_strict", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_type", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"json_valid", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
+    {"json_value", "json", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"load_aws_credentials", "aws", CatalogType::TABLE_FUNCTION_ENTRY},
     {"make_timestamptz", "icu", CatalogType::SCALAR_FUNCTION_ENTRY},
     {"mysql_clear_cache", "mysql_scanner", CatalogType::TABLE_FUNCTION_ENTRY},


Test patch:
diff --git a/test/sql/binder/test_alias_map_in_subquery.test b/test/sql/binder/test_alias_map_in_subquery.test
index f6cdf492a745..6b02c10f1d3e 100644
--- a/test/sql/binder/test_alias_map_in_subquery.test
+++ b/test/sql/binder/test_alias_map_in_subquery.test
@@ -36,7 +36,7 @@ INSERT INTO testjson VALUES ('{ "location" : { "address" : "123 Main St" }, "sam
 query I
 SELECT (WITH keys AS (SELECT unnest(json_keys(example)) AS k), nonNull AS (
     		SELECT keys.k, example->keys.k AS v
-    		FROM keys WHERE v IS NOT NULL
+    		FROM keys WHERE nullif(v, 'null') IS NOT NULL
     	)
     	SELECT json_group_object(nonNull.k, nonNull.v)
     	FROM nonNull
@@ -51,7 +51,7 @@ CREATE OR REPLACE MACRO strip_null_value(jsonValue) AS (
 	WITH keys AS (SELECT UNNEST(json_keys(jsonValue)) AS k),
 		nonNull AS (
 		SELECT keys.k, jsonValue->keys.k AS v
-		FROM keys WHERE v IS NOT NULL
+		FROM keys WHERE nullif(v, 'null') IS NOT NULL
 	)
 	SELECT json_group_object(nonNull.k, nonNull.v)
 	FROM nonNull
diff --git a/test/sql/json/issues/issue11804.test b/test/sql/json/issues/issue11804.test
index 014f7ba7460e..a21b7752f87c 100644
--- a/test/sql/json/issues/issue11804.test
+++ b/test/sql/json/issues/issue11804.test
@@ -18,3 +18,19 @@ query I
 select json_type(JSON '{"a": null}', '$.a') = 'NULL';
 ----
 true
+
+# Test issue 13436 - JSON_TYPE function produces wrong result if path is a column expression
+query II
+SELECT
+  json_type (json '{"a":1,"b":null}', p),
+  json_type (json '{"a":1,"b":null}', 'b')
+FROM (VALUES ('b')) AS t (p);
+----
+NULL	NULL
+
+# let's also test the extract many functionality
+query I
+select unnest(json_type(JSON '{"a": null}', ['$.a', '$.a'])) = 'NULL';
+----
+true
+true
diff --git a/test/sql/json/scalar/test_json_dot_syntax.test b/test/sql/json/scalar/test_json_dot_syntax.test
index d6c4aa10e3f6..774c772560ba 100644
--- a/test/sql/json/scalar/test_json_dot_syntax.test
+++ b/test/sql/json/scalar/test_json_dot_syntax.test
@@ -17,7 +17,7 @@ SELECT json_structure(json('{"duck":"goose"}').duck);
 query T
 select json('{"foo": null}').foo
 ----
-NULL
+null
 
 query T
 select json('{"foo": null}').foo.bar
@@ -27,7 +27,7 @@ NULL
 query T
 select json('null')
 ----
-NULL
+null
 
 query T
 select json('[null]')."0"
diff --git a/test/sql/json/scalar/test_json_exists.test b/test/sql/json/scalar/test_json_exists.test
new file mode 100644
index 000000000000..a50d0d27d0a6
--- /dev/null
+++ b/test/sql/json/scalar/test_json_exists.test
@@ -0,0 +1,39 @@
+# name: test/sql/json/scalar/test_json_exists.test
+# description: Test JSON exists
+# group: [scalar]
+
+require json
+
+statement ok
+pragma enable_verification
+
+query I
+SELECT json_exists('{"duck": null}', '$.duck')
+----
+true
+
+query I
+with path AS (
+    SELECT '$.duck' p
+)
+SELECT json_exists('{"duck": null}', p) FROM path
+----
+true
+
+query I
+SELECT json_exists('{"duck": null}', '$.goose')
+----
+false
+
+query I
+with path AS (
+    SELECT '$.goose' p
+)
+SELECT json_exists('{"duck": null}', p) FROM path
+----
+false
+
+query I
+SELECT json_exists('{"duck": null}', ['$.duck', '$.goose'])
+----
+[true, false]
diff --git a/test/sql/json/scalar/test_json_extract.test b/test/sql/json/scalar/test_json_extract.test
index 7bb2717919b1..d41b6a4e909e 100644
--- a/test/sql/json/scalar/test_json_extract.test
+++ b/test/sql/json/scalar/test_json_extract.test
@@ -17,7 +17,7 @@ SELECT json_structure('{"duck":"goose"}'->'duck');
 query T
 select json_extract('{"foo": null}', '$.foo')
 ----
-NULL
+null
 
 query T
 select json_extract('{"foo": null}', '$.foo.bar')
@@ -27,12 +27,12 @@ NULL
 query T
 select json_extract('null', '$')
 ----
-NULL
+null
 
 query T
 select json_extract('[null]', '$[0]')
 ----
-NULL
+null
 
 query T
 select json_extract('{"my_field": {"my_nested_field": ["goose", "duck"]}}', '/my_field/my_nested_field/1')
diff --git a/test/sql/json/scalar/test_json_path.test b/test/sql/json/scalar/test_json_path.test
index ed873dc252f4..05301e1ef057 100644
--- a/test/sql/json/scalar/test_json_path.test
+++ b/test/sql/json/scalar/test_json_path.test
@@ -214,7 +214,7 @@ select sum((to_json({duck:range})->'$.*')[1]::int) = sum(range) from range(10000
 query T
 select json_extract('[{"duck":null},{"duck":42},{"duck":null},{}]', '$[*].*')
 ----
-[NULL, 42, NULL]
+[null, 42, null]
 
 # test recursive wildcard path
 query T
@@ -245,7 +245,7 @@ with tbl as (
 )
 select json_extract(j, '$[*]..*') from tbl
 ----
-[42, {"a":43,"b":null}, 43, NULL]
+[42, {"a":43,"b":null}, 43, null]
 
 query T
 with tbl as (
diff --git a/test/sql/json/scalar/test_json_value.test b/test/sql/json/scalar/test_json_value.test
new file mode 100644
index 000000000000..30e0c1acd873
--- /dev/null
+++ b/test/sql/json/scalar/test_json_value.test
@@ -0,0 +1,193 @@
+# name: test/sql/json/scalar/test_json_value.test
+# description: Test JSON value
+# group: [scalar]
+
+require json
+
+statement ok
+pragma enable_verification
+
+# should go to our NULL
+query T
+select json_value('{"foo": null}', '$.foo')
+----
+null
+
+query T
+select json_value('{"foo": null}', '$.foo.bar')
+----
+NULL
+
+query T
+select json_value('null', '$')
+----
+null
+
+query T
+select json_value('[null]', '$[0]')
+----
+null
+
+query T
+select json_value('{"my_field": {"my_nested_field": ["goose", "duck"]}}', '/my_field/my_nested_field/1')
+----
+"duck"
+
+query T
+select json_value('{"my_field": {"my_nested_field": ["goose", "duckduckduckduck"]}}', '/my_field/my_nested_field/1')
+----
+"duckduckduckduck"
+
+query T
+select json_value('[1, 2, 42]', '2')
+----
+42
+
+# some sqlite tests
+# this one returns NULL because the extracted value is not scalar
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', '$');
+----
+NULL
+
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', '$.c');
+----
+NULL
+
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]');
+----
+NULL
+
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
+----
+7
+
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
+----
+NULL
+
+# list of paths must be constant
+statement error
+with tbl as (
+    select '{"a":2,"c":[4,5],"f":7}' j, ['$.c','$.a'] p
+)
+SELECT json_value(j, p);
+----
+Binder Error
+
+# first value is not scalar, so it is set to NULL
+query T
+SELECT json_value('{"a":2,"c":[4,5],"f":7}', ['$.c','$.a']);
+----
+[NULL, 2]
+
+query T
+SELECT json_value('{"a":2,"c":[4,5,{"f":7}]}', ['$.x', '$.a']);
+----
+[NULL, 2]
+
+query T
+SELECT json_value(NULL, ['$.x', '$.a']);
+----
+NULL
+
+statement ok
+CREATE TABLE t1(j varchar);
+
+statement ok
+INSERT INTO t1(j) VALUES('{"a":1,"b":[1,[2,3],4],"c":99}');
+
+query T
+SELECT json_value(j, '$.b[#]') FROM t1;
+----
+NULL
+
+query TT
+SELECT json_value(j, '$.a[#-1]'), json_value(j, '$.a[-1]') FROM t1;
+----
+NULL	NULL
+
+query TT
+SELECT json_value(j, '$.b[#-000001]') a, a = json_value(j, '$.b[-000001]') FROM t1;
+----
+4	true
+
+statement error
+SELECT json_value(j, '$.b[#-]') FROM t1;
+----
+
+statement error
+SELECT json_value(j, '$.b[-]') FROM t1;
+----
+
+statement error
+SELECT json_value(j, '$.b[#9]') FROM t1;
+----
+
+statement error
+SELECT json_value(j, '$.b[#+2]') FROM t1;
+----
+
+statement error
+SELECT json_value(j, '$.b[#-1') FROM t1;
+----
+
+statement error
+SELECT json_value(j, '$.b[#-1x]') FROM t1;
+----
+
+statement ok
+CREATE TABLE obj(x varchar);
+
+statement ok
+INSERT INTO obj VALUES('{"a":1,"b":2}');
+
+query T
+SELECT json_value(x, '$.b') FROM obj;
+----
+2
+
+query T
+SELECT json_value(x, '$."b"') FROM obj;
+----
+2
+
+statement ok
+CREATE TABLE t12(x varchar);
+
+statement ok
+INSERT INTO t12(x) VALUES(
+'{"settings":
+    {"layer2":
+       {"hapax.legomenon":
+          {"forceDisplay":true,
+           "transliterate":true,
+           "add.footnote":true,
+           "summary.report":true},
+        "dis.legomenon":
+          {"forceDisplay":true,
+           "transliterate":false,
+           "add.footnote":false,
+           "summary.report":true},
+        "tris.legomenon":
+          {"forceDisplay":true,
+           "transliterate":false,
+           "add.footnote":false,
+           "summary.report":false}
+       }
+    }
+ }');
+
+query T
+SELECT json_value(x, '$.settings.layer2."tris.legomenon"."summary.report"') FROM t12;
+----
+false
+
+query T
+SELECT json_value(x, '$.settings.layer2."tris.legomenon"') FROM t12;
+----
+NULL